PostgreSQL – Adding a Group of Rows Based on Column ID

postgresql

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

Best 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

Related Solutions

PostgreSQL Group Roles – Understanding and Managing

It sounds like what you probably want is to:

Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.
Now GRANT each user membership of the read-only access role with INHERIT.
For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.

Postgresql – Postgres get rows where a field stopps growing

Use the window function lead() in a subquery:

SELECT id, device_id, uptime
FROM  (
   SELECT *, lead(uptime) OVER (PARTITION BY device_id ORDER BY id) AS next_uptime
   FROM   tbl
   ) sub
WHERE  next_uptime < uptime

BTW, "where a field stops growing" would translate to <=, but "where the next row's timestamp is smaller" requires <.

Best Answer

Related Solutions

PostgreSQL Group Roles – Understanding and Managing

Postgresql – Postgres get rows where a field stopps growing

Related Question