SQL: Sum a column into multiple columns based on another column

user_9384866 0
Viewed 433

I have a table in a PostgreSQL that looks something like this:

Date            Amount      Type
2020-01-10      100         A
2020-01-10      150         B
2020-01-10      120         A
2020-01-10      90          B
2020-01-11      80          B
2020-01-11      120         A
2020-01-11      90          B

I would want to run a query that would output the sum of the days split based on the type and also compute the total so that it would output something like this

Date            A_Sum       B_Sum       Total
2020-01-10      220         240         460
2020-01-11      120         170         290

The SQL code I made so far works but I find it to be very inefficient as it is operating on multiple subqueries.

SELECT, q.A_Sum, q.B_Sum, q.A_Sum + q.B_Sum AS Total FROM 
(
 SELECT DISTINCT date,
  (SELECT SUM(amount) FROM table WHERE type='A' AND date=t.date) AS A_Sum,
  (SELECT SUM(amount) FROM table WHERE type='B' AND date=t.date) AS B_Sum
 FROM table t
) q

Is there a better and more efficient way to get the same output? How? As I did notice that running this query tend to take a significantly longer amount of time and I was hoping to speed this up.

postgresql sql
1 Answers

Use conditional aggregation -- which in Postgres, uses filter:

SELECT date,
       SUM(amount) FILTER (WHERE type = 'A') as a_sum,
       SUM(amount) FILTER (WHERE type = 'B') as b_sum,
       SUM(amount) as total
FROM table t
GROUP BY date;

It is unclear if the total is just "A"s and "B"s. If so, then:

SELECT date,
       SUM(amount) FILTER (WHERE type = 'A') as a_sum,
       SUM(amount) FILTER (WHERE type = 'B') as b_sum,
       SUM(amount) FILTER (WHERE type IN ('A', 'B')) as total
FROM table t
GROUP BY date;
user_11440350
Related