SQL syntax error inside CAST with case statement

Question

I want to list positive and negative bank_transfers from my DB. To do so I'm using below query:

SELECT
  bank_transfers.date AS day,
  CAST(SUM(CASE WHEN bank_transfers.amount_cents < 0 THEN bank_transfers.amount_cents END)) AS sum_expenses,
  CAST(SUM(CASE WHEN bank_transfers.amount_cents > 0 THEN bank_transfers.amount_cents END)) AS sum_revenue
FROM bank_transfers
JOIN customers ON customers.id = bank_transfers.customer_id
WHERE customers.id = 1
AND bank_transfers.date < '2020-11-26'
AND bank_transfers.date >= '2020-08-26'
GROUP BY DAY

But it gives me an error syntax error at or near ")". I guess it comes from the CAST so where should I close the parentheses?

Andronicus · Accepted Answer · 2020-11-26 11:46:35Z

2

You don't even need those casts. You're not casting into any type (unless sum_expenses and sum_revenue are your custom types).

Edit: If you want to cast to integer:

SELECT
  bank_transfers.date AS day,
  SUM(CASE WHEN bank_transfers.amount_cents < 0 THEN bank_transfers.amount_cents END)::integer AS sum_expenses,
  SUM(CASE WHEN bank_transfers.amount_cents > 0 THEN bank_transfers.amount_cents END)::integer AS sum_revenue
FROM bank_transfers
JOIN customers ON customers.id = bank_transfers.customer_id
WHERE customers.id = 1
AND bank_transfers.date < '2020-11-26'
AND bank_transfers.date >= '2020-08-26'
GROUP BY DAY

Or use cast(... as integer).

edited Nov 26, 2020 at 11:46

answered Nov 26, 2020 at 11:41

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mr_muscle Over a year ago

Without CAST I'll receive float number e.g. -4441.0 instead of -4441

Andronicus Over a year ago

@mr_muscle add ::integer or cast(... as integer). I've updated my answer

Andronicus Over a year ago

@mr_muscle no, CAST(SUM(CASE WHEN bank_transfers.amount_cents < 0 THEN bank_transfers.amount_cents END) as integer) AS sum_expenses

Gordon Linoff · Accepted Answer · 2020-11-26 12:50:34Z

0

You don't even need a JOIN for this query, because the customer id is in the bank_transfers table. I also strongly recommend FILTER for conditional aggregation:

SELECT bt.date AS day,
       SUM(bt.amount_cents) FILTER (WHERE bt.amount_cents < 0) as sum_expenses,
       SUM(bt.amount_cents) FILTER (WHERE bt.amount_cents > 0) as sum_revenue
FROM bank_transfers bt
WHERE bt.customer_id = 1 AND
      bt.date >= '2020-08-26' AND
      bt.date < '2020-11-26'
GROUP BY DAY;

answered Nov 26, 2020 at 12:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL syntax error inside CAST with case statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related