2 dimensional table query with join and PostgreSQL…

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:

applications: name
orders: date, …

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.

Answer

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.

2 dimensional table query with join and PostgreSQL [closed]

Advertisement

Answer