Link to home
Start Free TrialLog in
Avatar of countrymeister
countrymeister

asked on

How to skip rows from table where the Sum of Quantity is zero

I have a temp table #T1, which I do a final select on
One of the fields is a calculated column known as WeightedPrice
round(Sum(Price * Quantity )/SUM(Quantity), 4) as WeightedPrice,

the select clause is as follows

select Col1, Col2, Col3, Sum(Quantity), round(Sum(Price * Quantity )/SUM(Quantity), 4) as WeightedPrice

from table #T1
group by Col1, Col2, Col3

In the case when the sum of the Quantity is zero, it gives me a divide by zero error.
I need to skip these rows.
 
select Col1, Col2, Col3, Sum(Quantity), round(Sum(Price * Quantity )/SUM(Quantity), 4) as WeightedPrice

from table #T1
group by Col1, Col2, Col3

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial