How to Goupby in SQL into multiple columns

Question

I have a sql table which includes multiple articles with their corresponding sales transactions for different days. Here is an example table:

I need to group these table by date and sum up the sales of each product. The different products should now be showed as columns. So each article gets his own column. If no article is sold on a different date the table should be empty.

The result of above exampkle should look like this:

I already tried this SQL Code:

SELECT Date, AVG(Sales) FROM data GROUP BY Date ORDER BY Date asc;

This code just is just grouping the table and sums up the sales in one column.

Thank you in advance for your help!

score 2 · Accepted Answer · answered Jul 26 '19 at 06:05

2

use conditional aggregation with case when expression

SELECT Date, 
       sum(case when article='A' then Sales end) as articleA
       sum(case when article='B' then Sales end) as articleB
FROM data GROUP BY Date

answered Jul 26 '19 at 06:05

Fahmi

37,315
5
22
31

1

Thank you very much! This is working perfekt. – JMTaverne Jul 26 '19 at 07:08

score 0 · Answer 2 · answered Jul 26 '19 at 06:10

0

you can use IF as well

SELECT Date, 
       sum(if(article='A' , Sales,0)) as articleA
       sum(if(article='B', Sales ,0)) as articleB
FROM data GROUP BY Date

answered Jul 26 '19 at 06:10

Zaynul Abadin Tuhin

31,407
5
33
63

How to Goupby in SQL into multiple columns

2 Answers2