difficult (for me) postgres sql query

Question

Here are the tables I have:

AB tuple table C table which has entries with A.id, B.id, C.units D table which has entries with C.id I want to count all the entries in D table which have a C.id that has the same A.id and B.id and subtract that count from the sum of all C.units that have the same A.id and B.id as a new column "difference"

So I want the query to return the "difference", the common A.id and the common B.id in a single line

It should also return an entry if the count is 0 and the "difference" will just be be equal to sum(C.units)

For example

D table

D.id = 1, open=true, D.CID = 2
D.id = 2, open=true, D.CID = 3
D.id = 3, open=true, D.CID = 3
D.id = 4, open=true, D.CID = 4

C table

C.id = 2, A.id = 3, B.id = 5, units =4 
C.id = 3, A.id = 3, B.id = 5, units = 6
C.id = 4, A.id = 4, B.id = 6, units = 8
C.id = 5, A.id = 4, B.id = 6, units = 10

Bc the first 3 entries in D have CID's with the same AID and BID they are counted in the same entry. Also, the C entries that have the same A.id and B.id have their units summed. Even when a C entry has no associated D entry. Therefore, the query should return the following 2 entries:

1. difference = (6+4)-3 = 7    A.id = 3  B.id = 5
2. difference = (10+8)-1 = 17  A.id = 4  B.id = 6

kgrittn · Accepted Answer · 2012-09-13 18:53:13Z

1

Setup (which you really should include in your question):

CREATE TABLE c
  (
    id int NOT NULL PRIMARY KEY,
    aid int NOT NULL,
    bid int NOT NULL,
    units int NOT NULL
  );
CREATE TABLE d
  (
    id int NOT NULL PRIMARY KEY,
    open boolean NOT NULL,
    cid int NOT NULL
  );
INSERT INTO c VALUES (2,3,5,4),(3,3,5,6),(4,4,6,8),(5,4,6,10),(6,7,8,9);
INSERT INTO d VALUES (1,true,2),(2,true,3),(3,true,3),(4,true,4);

It's a little hard to understand the question, but I think you might be looking for something like this:

WITH n AS (
  SELECT aid, bid, count(*) AS cnt
    from c
    JOIN d ON (d.cid = c.id)
    GROUP BY aid, bid
)
SELECT aid, bid, sum(c.units) - COALESCE(n.cnt, 0) AS difference
  FROM c
  LEFT JOIN n USING (aid, bid)
  GROUP BY aid, bid, n.cnt
  ORDER BY aid, bid;

I get these results:

 aid | bid | difference 
-----+-----+------------
   3 |   5 |          7
   4 |   6 |         17
   7 |   8 |          9
(3 rows)

edited Sep 13, 2012 at 18:53

answered Sep 13, 2012 at 17:07

kgrittn

19.8k4 gold badges41 silver badges47 bronze badges

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

3 Comments

user1175817 Over a year ago

i think that does it for the most part. But there is one scenario left out which i mentioned which is if there are no d's such that d.cid = c.id but there is a c.id that has units, no entry shows up. The count in that scenario needs to be 0 and the difference would therefore be units - 0 = units. Is there a way to do that?

user1175817 Over a year ago

meaning none of the 0 counts get line items

kgrittn Over a year ago

Then you should show your table creation, population, and test run in your question, because it works for me.

Collectives™ on Stack Overflow

difficult (for me) postgres sql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related