CODEWITHSUNDEEP
mysqlaggregate-functions
Michael Stern
Michael Stern

Reputation: 487

SQL 'WHERE' restriction based on SUM

I am computing current positions by summing historical transactions, using

SELECT pairID, instrumentID, Sum(sharesTraded)
FROM cef_schema.pairadj_table 
GROUP BY pairID, instrumentID ORDER BY pairID;

This works fine, but produces responses for every pair even if the current position (sum of sharesTraded) is 0. I would prefer to omit these, but get an error if I try to restrict the SELECT with WHERE Sum(sharesTraded)!=0.

Is there some way to restrict the results to non-zero sums?

Upvotes: 0

Views: 37

Answers (2)

baao
baao

Reputation: 73251

You can use HAVING:

SELECT pairID, instrumentID, Sum(sharesTraded) as sharesTradedSum
FROM cef_schema.pairadj_table 
GROUP BY pairID, instrumentID 
HAVING sharesTradedSum > 0
ORDER BY pairID

Upvotes: 4

dashnick
dashnick

Reputation: 2060

Need to use HAVING with GROUP BY instead of WHERE.

Upvotes: 2

Related Questions