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.
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
;