current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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 http://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
share|improve this question
    
No this gives the sum for every single row that has the id of say Load Center-6, etc. –  user2769651 Jul 3 at 16:45
    
Could you edit your question with the expected results? –  Mihai Jul 3 at 16:55
    
edited.......... –  user2769651 Jul 3 at 16:59
    
So you want the total sum for only these 3 Equip_id? –  Mihai Jul 3 at 17:01
    
I'll clarify my post as to what I want. One second. –  user2769651 Jul 3 at 17:01

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.