Member Avatar for 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

  • 2 Contributors
  • 3 Replies
  • 120 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for 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
Member Avatar for jovillanuev

@urtrivedi , Thank you very much..

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

Edited by jovillanuev because: additional comment
Member Avatar for urtrivedi

because you are using "or" condition

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.