tota00 (3/4/2013)
This is fine. I have the total drop rate however I am also looking for the average drop rate per country as well as the average drop rate for the total. (Total rate / # of countries per model)Ie. IPhone 4 occurs Three times with droprate 1 an 2 and 3 for sweden and 2 and 4 and 6 for Norway then it should produce the average 2 for sweden and 4 for norway and total should be showing (countries + countries) divided by number of countries like (2+4)/2 in this case because it shouldn´t divide by countries that return null values when summaring and Dividing the total.
Hope I didn+t confuse you to much!
Thank´s in advance // TT
You need to calculate your averages using a GROUP BY and then add these back to your PIVOT.
Here's a start on your sample data:
DECLARE @Phones TABLE
(DropRate DECIMAL(38,12)
,Country VARCHAR(10)
,MarketingName VARCHAR(50)
,Manufacturer VARCHAR(50))
INSERT INTO @Phones
SELECT 0.000000000000,'Denmark','Zoarmon','Intel'
UNION ALL SELECT 0.000000000000,'Denmark','USB316','Sierra Wireless'
UNION ALL SELECT 0.000000000000,'Denmark','XP3300-AR1 (P25C005AA)','Sonim'
UNION ALL SELECT 0.000000000000,'Denmark','700C','Intermec Technologies Corp.'
UNION ALL SELECT 4.000000000000,'Denmark','R6230GE','BlackBerry'
UNION ALL SELECT 0.000000000000,'Denmark','SGH-S300','Samsung'
UNION ALL SELECT 0.000000000000,'Denmark','Treo 600','Palm'
UNION ALL SELECT 0.000000000000,'Sweden','Zoarmon','Intel'
UNION ALL SELECT 0.000000000000,'Sweden','USB316','Sierra Wireless'
UNION ALL SELECT 0.000000000000,'Sweden','XP3300-AR1 (P25C005AA)','Sonim'
UNION ALL SELECT 0.000000000000,'Sweden','700C','Intermec Technologies Corp.'
UNION ALL SELECT 2.000000000000,'Sweden','R6230GE','BlackBerry'
UNION ALL SELECT 0.000000000000,'Sweden','SGH-S300','Samsung'
UNION ALL SELECT 0.000000000000,'Sweden','Treo 600','Palm'
UNION ALL SELECT 0.000000000000,'Finland','Zoarmon','Intel'
UNION ALL SELECT 0.000000000000,'Finland','USB316','Sierra Wireless'
UNION ALL SELECT 0.000000000000,'Finland','XP3300-AR1 (P25C005AA)','Sonim'
UNION ALL SELECT 0.000000000000,'Finland','700C','Intermec Technologies Corp.'
UNION ALL SELECT 2.000000000000,'Finland','R6230GE','BlackBerry'
UNION ALL SELECT 0.000000000000,'Finland','SGH-S300','Samsung'
UNION ALL SELECT 0.000000000000,'Finland','Treo 600','Palm'
SELECT Country, Manufacturer, MarketingName, AvgDropRate=AVG(DropRate)
FROM @Phones
GROUP BY Country, Manufacturer, MarketingName
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