Postgres SQL Grouping By column value inside of Case statment

Question

t1:

AccountName	Date	Amount
A1	2022-06-30	2
A2	2022-06-30	1
A3	2022-06-30
A1	2022-07-31	4
A2	2022-07-31	5
A3	2022-07-31

I want to do a transformation on this table such that I fill in the "Amount" column of all rows with account name 'A3' and lets say that for each month group the 'A3' -"Amount" value is equal to (the 'A1' 'Amount' column + the 'A2' 'Amount' column), so the expected result table is:

AccountName	Date	Amount
A1	2022-06-30	2
A2	2022-06-30	1
A3	2022-06-30	3
A1	2022-07-31	4
A2	2022-07-31	5
A3	2022-07-31	9

The only way I can think of solving this is using multiple CTE's to separate each 'Date' value and using a case statements with multiple selects to get these values the using a union at the end:

with d1 as (
select * 
from t1 
WHERE Date = '2022-06-30'),

c1 as (
SELECT 
"AccountName", 
"Date",
Case WHEN "AccountName" = 'A3' 
THEN (SELECT "Amount" FROM t1 WHERE "AccountName" = 'A1') + 
(SELECT "Amount" FROM t1 WHERE AccountName = 'A2')
ELSE "Amount" END AS "Amount"
FROM d1),

d2 as (
select * 
from t1 
WHERE Date = '2022-07-31'),

c2 as (
SELECT 
"AccountName", 
"Date",
Case WHEN "AccountName" = 'A3' 
THEN (SELECT "Amount" FROM t1 WHERE "AccountName" = 'A1') + 
(SELECT "Amount" FROM t1 WHERE AccountName = 'A2')
ELSE "Amount" END AS "Amount"
FROM d2) 

SELECT * FROM c1
Union
SELECT * FROM c2

Is a better way of doing this? As i have multiple row calculations based on other row values and on top of that multiple Distinct 'Date' values (24) for which i would have to create separate CTE's for. This would result in an extremely long sql script for me. Is there maybe a way to group by every 'Date' value in the date column to avoid making multiple CTE's for each 'Date' Value? Additionally is there a better way to construct the sums values for the 'Amount' values for all 'A3' rows rather that using multiple selects in side each 'CASE WHEN'? Thanks!

Is it specifically "A1 + A2", or is it just "all the other accounts"? — Bergi
– Bergi, Commented Nov 17, 2022 at 2:39
Generally it would just be some operation based on other "AccountName"'s "Amount" values so "A1 + A2", or "A1 - A2", or "A1 / A2", — Guts
– Guts, Commented Nov 17, 2022 at 9:58
Not necessarily, sometimes in the real data set i'm transforming it'll be like n number of accounts that add up to another one, or potentially subtract — Guts
– Guts, Commented Nov 17, 2022 at 21:13
If it's subtract, then you need to do A1 - SUM(not-A1) - there can only be one minuend. — Bergi
– Bergi, Commented Nov 17, 2022 at 21:29

Bergi · Accepted Answer · 2022-11-17 12:57:02Z

1

You can use a window function for this - no need to hardcode the dates:

SELECT 
  "AccountName",
  "Date",
  (CASE WHEN "AccountName" = 'A3'
    THEN SUM("Amount") FILTER (WHERE "AccountName" IN ('A1', 'A2')) OVER (PARTITION BY "Date")
    ELSE "Amount"
  END) AS "Amount"
FROM t1

An equivalent query using subqueries would be

SELECT 
  "AccountName",
  "Date",
  (CASE WHEN "AccountName" = 'A3'
    THEN (
      SELECT SUM("Amount")
      FROM t1
      WHERE "Date" = outer."Date"
        AND "AccountName" IN ('A1', 'A2')
    )
    ELSE "Amount"
  END) AS "Amount"
FROM t1 outer

or, assuming that the amounts of A1 and A2 are never NULL:

SELECT 
  "AccountName",
  "Date",
  (CASE WHEN "AccountName" = 'A3'
    THEN (
      SELECT "Amount"
      FROM t1
      WHERE "Date" = t1out."Date"
        AND "AccountName" = 'A1'
    ) + (
      SELECT "Amount"
      FROM t1
      WHERE "Date" = t1out."Date"
        AND "AccountName" = 'A2'
    )
    ELSE "Amount"
  END) AS "Amount"
FROM t1 t1out

edited Nov 17, 2022 at 12:57

answered Nov 17, 2022 at 2:45

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

3 Comments

Guts Over a year ago

I'm going to have to go with the last one since sometimes the operations varys for my calculations(ie sometimes i have to do 'A1' - 'A2' or 'A1'/'A2' instead of just a SUM() ) Does this work for PSQL? I'm getting this error: Server error: Database Error in rpc request (from remote system) syntax error at or near "." LINE 16: WHERE "Date" = outer."Date" AND "AccountName" = 'Reven..

Guts Over a year ago

I used the window function version of this and it seems to work, but how would i adapt it the a situation where the operation for 'A3' is ('A1' - 'A2') or ('A1'/'A2') instead ?

Bergi Over a year ago

Oh, outer is a keyword, I forgot. I don't think you can use a window/aggregate function for division or subtraction.

Gerballi · Accepted Answer · 2022-11-17 01:24:55Z

0

This is how I would do it I think. The amount of rows without one becomes the sum of amounts in the same calendar month. I already combined month and year into a string, but it's probably way more efficient to compare the year and month value seperately but I like how this looks in joins.

update t1 t1update
set amount = (
 select sum(amount) from t1 
 where 
   extract(year from t1update.date date) || '-' || extract(month from t1update.date = 
   extract(year from t1.date date) || '-' || extract(month from t1.date)
)
where t1update.amount = '' or  t1update.amount is null

answered Nov 17, 2022 at 1:24

Gerballi

5113 silver badges6 bronze badges

2 Comments

Guts Over a year ago

how could you do this only using select statements (without the 'update' command)?

Gerballi Over a year ago

My first statement is update. It was either a join or a subquery and this seemed safest. Does it work?

Collectives™ on Stack Overflow

Postgres SQL Grouping By column value inside of Case statment

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related