CODEWITHSUNDEEP
sqlsql-serverselect
Processit
Processit

Reputation: 55

SQL Exclusion in the select query

I have a SQL Server query which is processing several thousands of rows. However the script runs fine, but I need to apply to just one of the select statements a criteria but not have it affect the rest of the results. 

Select
    Count ([key]) as KEYCOUNT, 
    Round (AVG ([AGE]),2) as AGE, 
    Round (AVG ([LENGTH]),2) as Length_X,
    Round (AVG ([Duration]),2) as DUR_Y,
from 
    [dbo].[XYZ]
where 
    [FLAG] = 1  and STAT = 3

The select I need to affect is Round (AVG ([LENGTH]),2) as Length_X, which I need to calculate as the average of only those lengths which are greater than 0, basically excluding all 0 (zeros)

Help much appreciated

Cheers

Upvotes: 0

Views: 141

Answers (2)

SMor
SMor

Reputation: 2862

which I need to calculate as the average of only those lengths which are greater than 0, basically excluding all 0 (zeros)

Which is just sum(Length) / sum(case Length when 0 then 0 else 1 end) with the appropriate casting if needed. E.g. 

set nocount on; 
with cte as (select * from (values  (0), (1), (2), (1), (0), (0)) as l(length))
select avg(cast(length as float)) as avg1, 
sum(cast(length as float)) / sum(case length when 0 then 0 else 1 end) as avg2 
from cte; 

with cte as (select * from (values  (1), (2), (1) ) as l(length))
select avg(cast(length as float)) as avg1, 
sum(cast(length as float)) / sum(case length when 0 then 0 else 1 end) as avg2 
from cte;

Notice the case condition. I used "not zero", you said "greater than zero". You choose.

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5916

You can use a case expression as the parameter of that AVG function:

Round(AVG(case when [LENGTH] > 0 then [LENGTH] end), 2) as Length_X,

This way all 0 values will be ignored by the AVG, while all other expressions in your query won't be affected.

Upvotes: 1

Related Questions