Thread: Including SQL Function in WHERE Clause

Currently, I am trying to select only those records whose SUM is greater than 0:

Code:

	strSQL = "SELECT tblStocks_detail.s_id, tblStocks.s_name, tblStocks.s_symbol, " & _
		"SUM(tblStocks_detail.sd_commission) As sd_commission, " & _
		"SUM(tblStocks_detail.sd_shares) As sd_shares, " & _
		"COUNT(tblStocks_detail.s_id) As sd_records, " & _
		"(SUM(tblStocks_detail.sd_shares * tblStocks_detail.sd_price_per_share) / SUM(tblStocks_detail.sd_shares)) As sd_price_per_share " & _
		"FROM tblStocks " & _
		"INNER JOIN tblStocks_detail ON tblStocks.s_id = tblStocks_detail.s_id " & _
		"WHERE SUM(tblStocks_detail.sd_shares) > 0 " & _
		"GROUP BY tblStocks_detail.s_id, tblStocks.s_name, tblStocks.s_symbol " & _
		"ORDER BY tblStocks.s_name"

However, this is generating the following error:

Cannot have aggregate function in WHERE clause (SUM(tblStocks_detail.sd_shares)>0).

What is the proper syntax? Reason being is that in my table, I might have some stock that have been sold. The way I show they have been sold, is to create a new record with the negative of the total.

For instance, if you owned 50 shares of a stock and sold them, you would have one record with positive 50 shares, and another with negative 50 shares. I do not want to show these records in my results.

After the GROUP BY clause put:
HAVING SUM(tblStocks_detail.sd_shares) > 0

Well that was certainly easy enough. Thanks Wolffy.