CODEWITHSUNDEEP
sqlgroup-bysum
P Wong
P Wong

Reputation: 1

SQL - better way to group and sum based on condition

I have data like:

Year Amount
2001 1
2020 2
2020 2
2021 5
2022 2
2022 2
2021 2

And I want to summarise data like:

Year Amount
2020 and before 5
2021 7
2022 4

I thought of 2 ways, and want to see whether there are any better method.

  1. Use Union
Select Year, sum(Amount) from TABLE_A 
where Year <= 2020
UNION
Select Year, sum(Amount) from TABLE_A 
where Year > 2020
  1. Added a new column
SELECT YR, SUM(Amount) FROM
(
  SELECT CASE WHEN Year <= 2020 THEN '2020_and_before') ELSE Year END as YR, * 
  FROM TABLE_A ) A
GROUP BY YR

I can get the results I want, but want to see whether there is better method

Upvotes: 0

Views: 39

Answers (1)

lemon
lemon

Reputation: 15482

In MySQL, you can avoid subqueries with a CASE expression in the following way:

SELECT CASE WHEN Year_ <= 2020 THEN "2020 or before"
            ELSE Year_ END AS Year_ ,
       SUM(Amount) AS total_amount
FROM tab       
GROUP BY 1

Output:

Year_ total_amount
2020 or before 5
2021 7
2022 4

Check a MySQL demo here.

Note: You should avoid messing with data types. If you're supposed to store years in the year field, you could leave the integer 2020, instead of having a string like "2020 or before".

Upvotes: 1

Related Questions