0
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

2
Contributors
3
Replies
4
Views
5 Years
Discussion Span
Last Post by urtrivedi
0

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
0

@urtrivedi , Thank you very much..

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

Edited by jovillanuev: additional comment

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.