CODEWITHSUNDEEP
sqlsql-server
gokufast
gokufast

Reputation: 21

How can i group dates in months (Jan, Feb, March, etc.)

Hi i got 1 table like this


+-------+------------+----------+---------+
| name  | date       | quantity | price   |
+-------+------------+----------+---------+
| art1  | 2017-01-05 | 10       | 5543.32 |
+-------+------------+----------+---------+
| art2  | 2017-01-15 | 16       | 12.56   |
+-------+------------+----------+---------+
| art3  | 2017-02-08 | 5        | 853.65  |
+-------+------------+----------+---------+
| art4  | 2017-03-06 | 32       | 98.65   |
+-------+------------+----------+---------+

And i want to show this information


+-------+------------+----------+---------+
| name  | January    | February | March   |
+-------+------------+----------+---------+
| art1  | 5543.32    | 0        | 0       |
+-------+------------+----------+---------+
| art2  | 12.56      | 0        | 0       |
+-------+------------+----------+---------+
| art3  | 0          | 853.65   | 0       |
+-------+------------+----------+---------+
| art4  | 0          | 0        | 98.65   |
+-------+------------+----------+---------+

How can i do that? I'm a bit rusty in sql. Thanks

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would use conditional aggregation:

select name,
       sum(case when date >= '2017-01-01' and date < '2017-02-01' then price else 0 end) as january,
       sum(case when date >= '2017-02-01' and date < '2017-03-01' then price else 0 end) as february,
       sum(case when date >= '2017-03-01' and date < '2017-04-01' then price else 0 end) as march
from t
group by name;

Upvotes: 1

Related Questions