Adding a group of rows in postgresql based on column id

Posted on

Question :

I have a table with a sets of rows such as displayed below:

Qty       Equip_ID
21487.92;"Load Center-6"
99272.59;"Load Center-10"
68088.61;"Load Center-11"
21821.5;;"Load Center-6"
102165.2;"Load Center-10"
72973.88;"Load Center-11"
21759.25;"Load Center-6"
102176.9;"Load Center-10"
63559.5;"Load Center-11"

How would I perform an addition operation and get the total of Load Center-6, 10 and 11?
That way I can get the total for each group (6, 10, 11)

I tried using this sql statement based from the answer on this stackoverflow post
https://stackoverflow.com/questions/9600587/how-to-sum-multiple-lines-in-sql
but I get an sql error since WHERE condition can’t be placed inside an SUM operation.

SELECT SUM(qty WHERE (equip_id = 'Load Center-6' AND equip_id = 'Load Center-10' AND equip_id = 'Load Center-11')) 
FROM td_m2_fact GROUP BY facility_code, period_start;

Trying the SQL statement:

SELECT SUM(qty),Equip_ID FROM table GROUP BY Equip_id

Results:
Qty            Equip_ID
672882459.189999;"Load Center-10"
124347378.953;"Load Center-6"
298342277.620001;"Load Center-11"

What I want:

Qty                                ID
SUM(21487.92,99272.59,68088.61)    Load Center-6,Load Center-10,Load Center-11
SUM(21821.5,102165.2,72973.88)     Load Center-6,Load Center-10,Load Center-11

Answer :

There is no natural order in a table. You need some indication which rows should go together. As discussed, a group_id per row could do the job.

Then the query becomes simple:

SELECT group_id, SUM(qty) AS qty, string_agg(equip_id, ', ') AS id
FROM   tbl
GROUP  BY 1;

If you already have your table and imported all the data, there is a trick. As long as you have not done anything to the table, yet, especially not updated or deleted any rows, chances are, the physical order of rows is still in the sequence like they were imported.

You could (ab)use the system column ctid as a poor man’s id to indicate this sort order and build on this. Assuming there are exactly 3 rows per group:

SELECT (rn + 2)/3, SUM(qty) AS qty, string_agg(equip_id, ', ') AS id
FROM   (SELECT *, row_number() OVER (ORDER BY ctid) AS rn FROM tbl) t
GROUP  BY 1

Leave a Reply

Your email address will not be published. Required fields are marked *