SUM of filtered parameters GROUP BY year in SQL-CodePudding

I have a table that looks as follows, and I need to show the SUM of small_bags values in selected regions (Chicago and California) GROUP BY years

Row	Date	Small_Bags	Large_Bags	region
1	2015-12-27	8603.62	93.25	Albany
2	2015-12-27	48605.95	17748.36	Atlanta
3	2015-12-27	142543.88	2367.22	BaltimoreWashington
4	2015-12-27	23520.19	5.69	Boise
5	2015-12-27	85913.6	99.26	Boston
6	2015-12-27	55236.68	0.0	BuffaloRochester
7	2015-12-27	1090140.07	110737.35	California

I have tried following code but it doest work (Scalar subquery produced more than one element)

Here's what I've tried

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

CodePudding user response：

Use conditional aggregation:

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;