postgresql merge 2 queries with different dates into 1 table-CodePudding

So I am new to postgres and would like to merge two queries into one. This is my table:

Queries:

SELECT area, SUM(volume) as "4/18/22 Volume"
FROM test
WHERE report_date = '2022-04-18'
GROUP BY area;

SELECT area, SUM(volume) as "4/25/22 Volume"
FROM test
WHERE report_date = '2022-04-25'
GROUP BY area;

Which give me this:

Now I would like the 2 volume columns next to each other, like:

area | 4/18/22 volume | 4/25/22 volume
---------------------------------------
area1|    331         |    265
area2|    520         |    248

How do I accomplish this?

CodePudding user response：

You can use CASE inside the SUM.

SELECT 
  area, 
  SUM(case when report_date = '2022-04-18' then volume else 0 end ) as "4/18/22 Volume",
  SUM(case when report_date = '2022-04-25' then volume else 0 end ) as "4/25/22 Volume"
FROM test
WHERE report_date = '2022-04-18' 
   OR report_date = '2022-04-25'
GROUP BY area;