HOw to get the average

[jovillanuev]

Hi Guys,

My requirements:
How do I get the overall average for the transaction given for every month.


My Query: This is the last part of my CTE Query
)
Select 
    Partno
    ,SUM(Case When TransMOnth='January' Then Usage2 else 0 End) As Jan
    ,SUM(Case When TransMOnth='February' Then Usage2 else 0 End) As Feb
    ,SUM(Case When TransMOnth='March' Then Usage2 else 0 End) As Mar
    ,SUM(Case When TransMOnth='April' Then Usage2 else 0 End) As Apr
    ,SUM(Case When TransMOnth='May' Then Usage2 else 0 End) As May
    ,SUM(Case When TransMOnth='June' Then Usage2 else 0 End) As Jun
    ,SUM(Case When TransMOnth='July' Then Usage2 else 0 End) As Jul
    ,SUM(Case When TransMOnth='August' Then Usage2 else 0 End) As Aug
    ,SUM(Case When TransMOnth='September' Then Usage2 else 0 End) As Sep
    ,SUM(Case When TransMOnth='October' Then Usage2 else 0 End) As Oct
    ,SUM(Case When TransMOnth='November' Then Usage2 else 0 End) As Nov
    ,SUM(Case When TransMOnth='December' Then Usage2 else 0 End) As Dec
Into #Data2
From CTE2
Group by Partno
Having SUM(Case When TransMOnth='January' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='February' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='March' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='April' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='May' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='June' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='July' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='August' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='September' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='October' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='November' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='December' Then Usage2 else 0 End) > 0
Order by Partno


SAmple Result:


Partno               |Apr|May|Jun|Overall Ave
---------------------------------------------
COR3003110ENLIGHTEN  |105|197|51|118
HTC1019              |190|89 |69|116

Thank you in Advance..
JOV

[urtrivedi]

I have added on line ",avg(usage2) overall_avg"

Select 
    Partno
    ,SUM(Case When TransMOnth='January' Then Usage2 else 0 End) As Jan
    ,SUM(Case When TransMOnth='February' Then Usage2 else 0 End) As Feb
    ,SUM(Case When TransMOnth='March' Then Usage2 else 0 End) As Mar
    ,SUM(Case When TransMOnth='April' Then Usage2 else 0 End) As Apr
    ,SUM(Case When TransMOnth='May' Then Usage2 else 0 End) As May
    ,SUM(Case When TransMOnth='June' Then Usage2 else 0 End) As Jun
    ,SUM(Case When TransMOnth='July' Then Usage2 else 0 End) As Jul
    ,SUM(Case When TransMOnth='August' Then Usage2 else 0 End) As Aug
    ,SUM(Case When TransMOnth='September' Then Usage2 else 0 End) As Sep
    ,SUM(Case When TransMOnth='October' Then Usage2 else 0 End) As Oct
    ,SUM(Case When TransMOnth='November' Then Usage2 else 0 End) As Nov
    ,SUM(Case When TransMOnth='December' Then Usage2 else 0 End) As Dec
    ,avg(usage2) overall_avg
Into #Data2
From CTE2
Group by Partno
Having SUM(Case When TransMOnth='January' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='February' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='March' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='April' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='May' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='June' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='July' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='August' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='September' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='October' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='November' Then Usage2 else 0 End) > 0
    or SUM(Case When TransMOnth='December' Then Usage2 else 0 End) > 0
Order by Partno

[jovillanuev]

@urtrivedi , Thank you very much..

Why the having clause condition has no effect. still the zero values are displaying.

[urtrivedi]

because you are using "or" condition