CODEWITHSUNDEEP
sqlms-access
jDave1984
jDave1984

Reputation: 962

SQL Sum, Return Field that aren't Zero

I've written a query to get the Sum of three fields, except the majority of them sum up to be zero. I need to only return the sums that are greater than zero. Below is the Query

SELECT [Product], 
SUM([Pounds]) as SumPounds, 
SUM([Dollars]) as SumDollars, 
SUM([EBITDA]) as SumEBITDA 
FROM MyTable 
WHERE ([Year] = '2017' AND [Date] <= #8/20/2017#) 
SORT BY [Product];

Is there a way to only return the sums greater than zero in Access?

Upvotes: 0

Views: 131

Answers (2)

juergen d
juergen d

Reputation: 204746

SELECT [Product], 
       SUM([Pounds]) as SumPounds, 
       SUM([Dollars]) as SumDollars, 
       SUM([EBITDA]) as SumEBITDA 
FROM MyTable 
WHERE ([Year] = '2017' AND [Date] <= #8/20/2017#) 
GROUP BY [Product]
HAVING SUM([Pounds]) + SUM([Dollars]) + SUM([EBITDA]) > 0

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You can use HAVING for filter the result of aggregated functions 

SELECT [Product], 
SUM([Pounds]) as SumPounds, 
SUM([Dollars]) as SumDollars, 
SUM([EBITDA]) as SumEBITDA 
FROM MyTable 
WHERE ([Year] = '2017' AND [Date] <= #8/20/2017#) 
HAVING UM([Pounds]) >0  AND SUM([Dollars]) > 0 AND SUM([EBITDA]) >0
SORT BY [Product];

if you need all the 3 value you can use AND as in answer above if you need only one you can use OR

Upvotes: 1

Related Questions