CODEWITHSUNDEEP
mysql
DanielAttard
DanielAttard

Reputation: 3595

MySQL query group by with multiple sum values

If you review this SQL Fiddle, you will see that the resulting data is grouped by Year and Type which means that there are 2 rows for each year. I would like to only group on the year, and display the Sum of the A-type and the B-type on the same row.

Can someone please tell me how I would modify the sum statements in this query so that the first sum is only for the A-type, and the second sum is for the B-type? Thanks.

Upvotes: 1

Views: 1091

Answers (2)

Wolph
Wolph

Reputation: 80011

How about this: http://sqlfiddle.com/#!2/85415/11

SELECT
  SUM(IF(TYPE = 'A', Amt, 0)) AS A_amt,
  SUM(IF(TYPE = 'B', Amt, 0)) AS B_amt,
  YEAR,
  TYPE
FROM YourTable
GROUP BY YEAR

Alternatively you could use a CASE statement which is standard SQL, but slightly longer so I opted for this version.

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

If you just want the two columns, it's fairly easy to do;

SELECT Year, 
  SUM(CASE WHEN Type='A' THEN Amt ELSE 0 END) TypeA,
  SUM(CASE WHEN Type='B' THEN Amt ELSE 0 END) TypeB
FROM YourTable
GROUP BY Year

An SQLfiddle to test with.

...or a bit more verbose standard SQL;

Upvotes: 3

Related Questions