SQL: Group by date, and sum-CodePudding

I have the following table in the database:

date                 account_id  currency     balanceUSD
01-01-2022 17:17:25  1           USD          1000
01-01-2022 17:17:25  1           EUR          1200
01-01-2022 23:14:34  1           USD          1050
01-01-2022 23:14:34  1           EUR          1350
01-02-2022 15:14:42  1           USD          1040
01-02-2022 15:14:42  1           EUR          1460
01-02-2022 20:17:45  1           USD          1030
01-02-2022 20:17:45  1           EUR          1550
01-01-2022 17:17:25  2           USD          3000
01-01-2022 17:17:25  2           EUR          2300
01-01-2022 23:14:34  2           USD          3200
01-01-2022 23:14:34  2           EUR          1450
01-02-2022 15:14:42  2           USD          3350
01-02-2022 15:14:42  2           EUR          1850
01-02-2022 20:17:45  2           USD          3400
01-02-2022 20:17:45  2           EUR          1900

What I want to do is group by (year, month, day) and account_id and sum the balanceUSD. i.e.

date         account_id     balanceUSD
01-01-2022   1              4600
01-02-2022   1              5080
01-01-2022   2              9950
01-02-2022   2              10500

How can this be done?

CodePudding user response：

We can use the function date_trunc('day', rental_date) to extract the date from the timestamp.

SELECT 
  date_trunc('day', date) as "date",
  account_id,
  sum(balanceUSD) as "balanceUSD"
FROM 
  account_id,
  table_name
GROUP BY 
  account_id
  date_trunc('day', date) 
ORDER BY 
  account_id,
  date_trunc('day', date) ;