June 13, 2012 at 11:47 pm
Hi Forumer's
How could i get the overall average for Jan to Dec transaction
i want to incorporate it on my script. below is the last part of my CTE script.
My Script..
)
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
Partno |Apr|May|Jun| Overall Average
---------------------------------------------
COR3003110ENLIGHTEN |105|197|51|118
HTC1019 |190|89 |69|116
Thank you in Advance..
June 14, 2012 at 12:24 am
I'm 99.99% certain that the posted SQL will generate a syntax error.
I'd suggest an answer (I know how to do this) but I'd want to put it into a syntactically correct context.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 14, 2012 at 12:33 am
I am not sure why you are having the HAVING clause
Even if one of the months have values, you will get 13 columns and not 3 or 4 as you have shown in the results
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
,ROUND( CAST( SUM(Usage2) AS NUMERIC(18,2) )/COUNT(DISTINCT TransMOnth), 0 ) AS [Overall Average]
--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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 14, 2012 at 12:53 am
I gave only the 3 months data as my sample.
I place having condition clause as i will run this scripts
for the past 3 month of the current month and i will not display
those with zero values. any idea guys, even some of the months has zero values still they are diplay in the result., i thnk the having condition has no effects.
btw,
i add this AVG(Usage2) as Overall_AVG to get the overall average
is the zero has an effect in avg result?
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
June 14, 2012 at 3:47 am
You will probably need Dynamic SQL for that
Post some sample data and the DDL of the the tables involved
This will make it easier for people to come up with tested solutions
Please check the link in my signature to know how to do this if you don't know how to do it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 14, 2012 at 4:31 am
I'm still wondering where CTE2 is defined.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 14, 2012 at 5:12 am
dwain.c (6/14/2012)
I'm still wondering where CTE2 is defined.
Its probably defined in the code the OP has
The OP has only posted the final SELECT statement
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply