我有一個如下所示的表，我需要顯示選定區域（芝加哥和加利福尼亞）GROUP BY年份中small_bags值的總和
| 排 | 日期 | 小包 | 大包 | 地區 |
|---|---|---|---|---|
| 1 | 2015-12-27 | 8603.62 | 93.25 | 奧爾巴尼 |
| 2 | 2015-12-27 | 48605.95 | 17748.36 | 亞特蘭大 |
| 3 | 2015-12-27 | 142543.88 | 2367.22 | 巴爾的摩華盛頓 |
| 4 | 2015-12-27 | 23520.19 | 5.69 | 博伊西 |
| 5 | 2015-12-27 | 85913.6 | 99.26 | 波士頓 |
| 6 | 2015-12-27 | 55236.68 | 0.0 | 布法羅羅切斯特 |
| 7 | 2015-12-27 | 1090140.07 | 110737.35 | 加利福尼亞 |
我嘗試了以下代碼，但它不起作用（標量子查詢產生了多個元素）
這是我嘗試過的
SELECT
EXTRACT(YEAR FROM Date) AS year,
SUM(
(SELECT
Small_Bags
FROM
`training-363808.avocado_data.avocado_prices`
WHERE
region = 'Chicago'
))
AS total_small_bags_Chicago,
SUM(
(SELECT
Small_Bags
FROM
`training-363808.avocado_data.avocado_prices`
WHERE
region = 'California'
))
AS total_small__bags_California
FROM
`training-363808.avocado_data.avocado_prices`
GROUP BY
year
uj5u.com熱心網友回復：
使用條件聚合：
SELECT
YEAR(DATE) AS year,
SUM(CASE WHEN region = 'Chicago'
THEN Small_Bags ELSE 0 END) AS total_small_bags_Chicago,
SUM(CASE WHEN region = 'California'
THEN Small_Bags ELSE 0 END) AS total_small_bags_California
FROM training-363808.avocado_data.avocado_prices
GROUP BY 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/517931.html
標籤：mysqlsql
上一篇：SQL：將陣列回傳值指定為元組
下一篇：如何根據多列中的值進行查詢