Trying to return a 2 dimensional table from SQL query (Postgres). Read about Pivot Table (but not sure if it is valid for join) and crosstab/tablefunc but no luck.
Expected result
App A | App B
2020-01-01 8 | 2
2020-02-01 14 | 5
2020-03-01 13 | 0
I have 2 tables:
I'm looking to count orders by month and application.
My attempt so far.
SELECT date_trunc('month', date) as date, count(1), x.name
FROM (SELECT date_trunc('month', date) as date, application_id
FROM orders
GROUP BY date, application_id) o
JOIN applications x ON o.application_id = x.id AND x.id in (1, 2, 5)
WHERE
o.date > NOW() - '9 months'::interval
group by date, x.name
order by date, x.name
which returns
date count name
2020-01-01 8 App A
2020-01-01 2 App B
2020-02-01 14 App A
2020-02-01 5 App B
Thanks.
To pivot your resultset over a fixed list of values, you can do conditional aggregation:
select date_trunc('month', o.date) date,
count(*) filter(where a.name = 'App A') app_a,
count(*) filter(where a.name = 'App B') app_b
from applications a
inner join orders o on o.application_id = a.id
where o.date > date_trunc('month', current_date) - '9 months'::interval
group by 1
order by 1
Note that I modified the where clause so it filters on entire months.