PostrgreSQL 8.4: Summation by account numbers

Question

I am doing some report on financial accounts, and I need to sum values at levels depending on the initial numbers... For example sum all values for account starting with 0 (01, 011, 012..), or starting with 1 (1, 10, 111...), or starting with 111 (111,1112,1113...) etc.

Here is simplified sample table:

CREATE TABLE account(id, acctNo, credit) AS ( VALUES (1, '01', 100) ,(2, '011', 200) ,(3, '0112', 300) ,(4, '014', 400) ,(5, '0144', 500) ,(6, '0148', 600) ,(7, '01120', 100) ,(8, '01121', 100) ,(9, '0140', 50) ,(10,'02', 50) ,(11,'021', 50) ,(12,'1', 50) ,(13,'10', 100) ,(15,'100', 50) ,(14,'1021', 50) ,(16,'202', 50) ,(17,'221', 50) ,(18,'4480', 50) ,(19,'447', 50) ,(20,'5880', 50) )

I managed to do it but it is kinda robust SQL, is there some better solution? Here is code:

WITH a AS (SELECT SUBSTRING(acctNo,1,1) AS LEVEL, SUM(credit) AS sum1 FROM account GROUP BY LEVEL ORDER BY LEVEL), b AS (SELECT SUBSTRING(acctNo,1,2) AS level2, SUM(credit) FROM account GROUP BY level2 ORDER BY level2), c AS (SELECT SUBSTRING(acctNo,1,3) AS level3, SUM(credit) FROM account GROUP BY level3 ORDER BY level3), d AS (SELECT SUBSTRING(acctNo,1,4) AS level4, SUM(credit) FROM account GROUP BY level4 ORDER BY level4), e AS (SELECT SUBSTRING(acctNo,1,5) AS level5, SUM(credit) FROM account GROUP BY level5 ORDER BY level5) SELECT * FROM (SELECT a.* FROM a UNION (SELECT b.* FROM b WHERE char_length(level2)>=2) UNION (SELECT c.* FROM c WHERE char_length(level3)>=3) UNION (SELECT d.* FROM d WHERE char_length(level4)>=4) UNION (SELECT e.* FROM e WHERE char_length(level5)>=5)) a ORDER BY LEVEL

This is only for 5 levels(five-digit numbers)...Is there some generic solution? What if tomorrow I'll need for 6 levels, etc...

Here is SQL Fiddle

Thanks.

Maybe you should learn about 1NF, first ?

wildplasser
– wildplasser

2013-06-05 14:59:19 +00:00
Commented Jun 5, 2013 at 14:59 — wildplasser
– wildplasser, Commented Jun 5, 2013 at 14:59

maniek · Accepted Answer · 2013-06-05 15:08:22Z

3

Yay generate_series!

SELECT substring(a.acctNo from 1 for g.g) as level, sum(a.credit) FROM account a, generate_series(1,5) g where length(a.acctNo) >= g.g group by 1 order by 1

answered Jun 5, 2013 at 15:08

maniek

7,3252 gold badges24 silver badges44 bronze badges

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

2 Comments

Gordon Linoff Over a year ago

+1 . . . I very much like this solution, although I do prefer cross join to the use of a , in the from clause.

maniek Over a year ago

@GordonLinoff Heh, I just thought about this monstrosity:

SELECT substring(a.acctNo from 1 for generate_series(1,length(a.acctno))) as level, sum(a.credit) FROM account a group by 1 order by 1

. Somewhat "cleaner", possibly more performant, but a lot harder to understand.

Clodoaldo Neto · Accepted Answer · 2013-06-05 16:15:19Z

To make maniek's answer generic:

with levels as ( select g from generate_series( 1, (select max(length(acctNo)) from account) ) g ) select substring(acctNo from 1 for l.g) as level, sum(a.credit) from account a, levels l where length(acctNo) >= l.g group by 1 order by 1

Collectives™ on Stack Overflow

PostrgreSQL 8.4: Summation by account numbers

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related