How to get a sum of all rows that meets condition in postgres

Question

I am trying to return sums with their specific conditions.

SELECT 
  COUNT(*),
  SUM("transactionTotal" WHERE "entryType"=sold) as soldtotal,
  SUM(case when "entryType"=new then "transactionTotal" else 0 end) as newtotal
FROM "MoneyTransactions"
WHERE cast("createdAt" as date) BETWEEN '2020-10-08' AND '2020-10-09'

I am trying to sum up the rows with "entryType"=sold and "entryType"=new and return those values separately. obviously both my logic are wrong. can someone lend a hand.

Answer 1

You were on the right track to use conditional aggregation, but your syntax is slightly off. Try this version:

SELECT 
    COUNT(*) AS total_cnt,
    SUM(transactionTotal) FILTER (WHERE entryType = 'sold') AS soldtotal,
    SUM(transactionTotal) FILTER (WHERE entryType = 'new')  AS newtotal
FROM MoneyTransactions
WHERE
    createdAt::date BETWEEN '2020-10-08' AND '2020-10-09';

Note: Assuming your createdAt column already be date, then there is no point in casting it. If it is text, then yes you would need to convert it, but you might have to use TO_DATE depending on its format.