CODEWITHSUNDEEP
postgresqlpsql
IamH1kc
IamH1kc

Reputation: 6772

How to compute the sum of group by and return for a condition in psql?

Table 1

Name Quantity     Type     Value   Date
Car    1            B         10,000  time-stamp1
Car    2            S          10,000  time-stamp2
Bus    1            B          10,000  time-stamp3

So, that's my table structure. I want my sql query to group the results based on name and only return me the result where Quantity of type S > Quantity of type B. Though I'm able to do a group by, I'm not sure how to do a Sum of the Group By and only return for the particular condition Quantity of type S > Quantity of type B.

Upvotes: 0

Views: 638

Answers (1)

krokodilko
krokodilko

Reputation: 36117

You need to do it in two steps.
First calculate quantities for S and B using sum with CASE expressions:

SELECT name,
       sum( CASE WHEN Type = 'B' THEN Quantity ELSE 0 END ) As q_b,
       sum( CASE WHEN Type = 'S' THEN Quantity ELSE 0 END ) As q_s,
       sum( Quantity ) As q
FROM Table1
GROUP BY name
;

then select only requred rows from the result of the above query:

SELECT *
FROM (
   SELECT name,
          sum( CASE WHEN Type = 'B' THEN Quantity ELSE 0 END ) As q_b,
          sum( CASE WHEN Type = 'S' THEN Quantity ELSE 0 END ) As q_s,
          sum( Quantity ) As q
   FROM Table1
   GROUP BY name
) s
WHERE q_s > q_b
;

Demo: http://sqlfiddle.com/#!15/f394f/2

Upvotes: 1

Related Questions