How to calculate sums of different categories for each day?

Question

PostgreSQL

Hey Ya!
There is a table:

CREATE TABLE Table1
    ("user_id" int, "date" timestamp, "amount" int, "currency" varchar(3))
;
    
INSERT INTO Table1
    ("user_id", "date", "amount", "currency")
VALUES
    (1, '2016-03-28 00:00:00', 10, 'USD'),
    (1, '2016-03-28 00:00:00', 13, 'USD'),
    (1, '2016-03-28 00:00:00', 15, 'USD'),
    (1, '2016-03-28 00:00:00', 18, 'USD'),
    (1, '2016-03-28 00:00:00', 16, 'EUR'),
    (1, '2016-03-29 00:00:00', 10, 'EUR'),
    (1, '2016-03-29 00:00:00', 13, 'EUR'),
    (1, '2016-03-29 00:00:00', 15, 'USD'),
    (1, '2016-03-29 00:00:00', 18, 'USD'),
    (1, '2016-03-29 00:00:00', 16, 'USD'),
    (1, '2016-03-30 00:00:00', 11, 'USD'),
    (1, '2016-03-30 00:00:00', 12, 'EUR'),
    (1, '2016-03-30 00:00:00', 19, 'EUR'),
    (1, '2016-03-30 00:00:00', 17, 'EUR'),
    (1, '2016-03-30 00:00:00', 13, 'USD'),
    (1, '2016-03-30 00:00:00', 12, 'EUR'),
    (1, '2016-03-30 00:00:00', 16, 'EUR')
;

You need to get sums for each currency for each day. What would be like:

user_id | date       | usd | eur
--------+------------+-----+-----
1       | 2016-03-28 | 56  | 16
--------+------------+-----+-----
1       | 2016-03-29 | 49  | 23
--------+------------+-----+-----
1       | 2016-03-30 | 24  | 76

There is a condition that there can be several currencies, and they cannot be strictly hammered into the request. I now understand that you need to select which currencies are in the table and then make a selection for each currency and summarize. But I don't understand how to write all this into the result.

Answer 1

Postgresql 9.4 and later can read more readable syntax from SQL:2003 instead of the long CASE in the nozzy example

select 
user_id,
date,
SUM(amount) filter(where currency = 'USD') AS USD,
SUM(amount) filter(where currency = 'USD') AS EUR
FROM Table1
GROUP BY user_id, date

You will have to enter the list of possible currencies in the request.
Or additionally group by currency and rake already on the application.

Answer 2

If you do not pay attention to the fact that you fantasized two new columns, then it's easy:

SELECT Table1.user_id,
       Table1.`date`,
       SUM(Table1.amount) as sum,
       Table1.currency
  FROM new_site.Table1 Table1
GROUP BY Table1.currency, Table1.`date`

And if you need to generate columns, then alas, you need to know them in advance and add them dynamically to the query:

SELECT 
Table1.user_id, 
Table1.`date`, 
(SELECT SUM(amount) FROM Table1 tbl2 WHERE tbl2.`date` = Table1.`date` AND tbl2.currency = 'USD') as USD,
(SELECT SUM(amount) FROM Table1 tbl2 WHERE tbl2.`date` = Table1.`date` AND tbl2.currency = 'EUR') as EUR
  FROM new_site.Table1 Table1
GROUP BY Table1.`date`

Answer 3

select 
user_id,
date,
SUM(CASE WHEN currency = 'USD' THEN amount ELSE 0 END) AS USD,
SUM(CASE WHEN currency = 'EUR' THEN amount ELSE 0 END) AS EUR
FROM Table1 AS
GROUP BY user_id, date

or use IF:

SUM( IF(currency = 'USD', amount, 0) ) AS USD,
SUM( IF(currency = 'EUR', amount, 0) ) AS EUR