CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
Shisa
Shisa

Reputation: 580

PostgreSQL group data by month AND type

I have a table which goes something like this:

ID    item_type    date
1      apple        2016-12-01       
2      banana       2016-12-01      
3      banana       2016-12-01

and so on. What I need to do is get an output table with months as rows, and item_type as columns, and the count of number of entries for each combo so that it looks something like

month    apple     banana       
dec      1         2

and so on... I've tried date_trunc for the monthly grouping but that seems to be giving me the count on basis of date not month! And I'm not sure how to do the multiple grouping at all.

Upvotes: 2

Views: 11039

Answers (3)

Amateur
Amateur

Reputation: 113

Btw, if I am not mistaken, the query should be like this: 

select date_trunc('month', date) as mon,
       sum((item_type = 'apple')::int) as apple,
       sum((item_type = 'banana')::int) as banana
from t
group by mon
order by mon;

To avoid Postgres to issue an error, since I assume "item_type" is stored as a string variable.

Upvotes: 0

Thanos
Thanos

Reputation: 340

irrelevant tip but I would also not use "date" as the column name as it's a reserved word in pgsql.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Use conditional aggregation:

select date_trunc('month', date) as mon,
       sum((item_type = apple)::int) as apple,
       sum((item_type = banana)::int) as banana
from t
group by mon
order by mon;

Upvotes: 5

Related Questions