Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tota00
Starting Member
10 Posts |
Posted - 2013-03-04 : 08:40:55
|
Hi!First thanks for the help I got a couple of weeks ago! Now to my new proble that is to change this statement a bit as it works halfway...SELECT *, ISNULL(Denmark,0) + ISNULL(Finland,0) + ISNULL(Lithuania,0) + ISNULL(Norway,0) + ISNULL(Sweden,0) AS Total FROM (SELECT DropRate,MarketingName, Manufacturer, Country FROM ReportedNetInfoData) s PIVOT (SUM(DropRate) FOR Country IN ([Denmark], [Finland], [Lithuania], [Norway], [Sweden]))PThe simplified table looks like this:DropRate Country MarketingName Manufacturer0.000000000000 Denmark Zoarmon Intel0.000000000000 Denmark USB316 Sierra Wireless0.000000000000 Denmark XP3300-AR1 (P25C005AA) Sonim0.000000000000 Denmark 700C Intermec Technologies Corp.4.762000000000 Denmark R6230GE BlackBerry0.000000000000 Denmark SGH-S300 Samsung0.000000000000 Denmark Treo 600 PalmThe result from the sql-statement is something like this:(the null values under all countries except denmark depends on no data from them yet)MarketingName Manufacturer Denmark Finland Lithuania Norway Sweden Total5130 XpressMusic Nokia 153.725000000000 NULL NULL NULL NULL 153.7250000000003310 Nokia 151.236000000000 NULL NULL NULL NULL 151.2360000000001100 Nokia 151.206000000000 NULL NULL NULL NULL 151.206000000000iPhone 4 Apple 144.371000000000 NULL NULL NULL NULL 144.3710000000001208 Nokia 133.742000000000 NULL NULL NULL NULL 133.742000000000iPhone 4S Apple 130.181000000000 NULL NULL NULL NULL 130.181000000000X1-01 Nokia 129.931000000000 NULL NULL NULL NULL 129.931000000000SGH-E250 Samsung 118.778000000000 NULL NULL NULL NULL 118.7780000000006300 Nokia 112.905000000000 NULL NULL NULL NULL 112.905000000000101, 1010 Nokia 110.319000000000 NULL NULL NULL NULL 110.319000000000This 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 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-04 : 23:56:43
|
do you mean this?SELECT *, ISNULL(Denmark,0) + ISNULL(Finland,0) + ISNULL(Lithuania,0) + ISNULL(Norway,0) + ISNULL(Sweden,0) AS Total FROM (SELECT DropRate,MarketingName, Manufacturer, Country ,AVG(DropRate)OVER (PARTITION BY Country) AS CntAvgFROM ReportedNetInfoData) s PIVOT (SUM(DropRate) FOR Country IN ([Denmark], [Finland], [Lithuania], [Norway], [Sweden]))P ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tota00
Starting Member
10 Posts |
Posted - 2013-03-05 : 04:27:34
|
Hivisakh16!Not exaktly but that gave me some idéeas for the future :-)Maybe my new explanation is a Little bit clearer!Okey!I have come a bit further on the way to a solution and have included a script for creating a test-table with sample dummy data in it!The result from the sql-statement now works fine except the total sum result that has to be divided by the number of columns that do not return null values or is greater then zero so in this case with the sample data it should be divided by 3 but how do I solve that?Sql statement:SELECT *, ISNULL(Denmark,0) + ISNULL(Finland,0) + ISNULL(Lithuania,0) + ISNULL(Norway,0) + ISNULL(Sweden,0) AS Total FROM (SELECT DropRate,MarketingName, Manufacturer, Country FROM tblTest) s PIVOT (AVG(DropRate)FOR Country IN ([Denmark], [Finland], [Lithuania], [Norway], [Sweden]))P Generates output:MarketingName Manufacturer Denmark Finland Lithuania Norway Sweden TotaliPhone 3G Apple 1.408000000000 1.485000000000 0.000000000000 NULL 2.625000000000 5.518000000000What I need is:Should be: (difference is the division by three in this example)SELECT *, (ISNULL(Denmark,0) + ISNULL(Finland,0) + ISNULL(Lithuania,0) + ISNULL(Norway,0) + ISNULL(Sweden,0)) / 3 AS Total FROM (SELECT DropRate,MarketingName, Manufacturer, Country FROM tblTest) s PIVOT (AVG(DropRate)FOR Country IN ([Denmark], [Finland], [Lithuania], [Norway], [Sweden]))P That generates output:MarketingName Manufacturer Denmark Finland Lithuania Norway Sweden TotaliPhone 3G Apple 1.408000000000 1.485000000000 0.000000000000 NULL 2.625000000000 1.839333333333But from time over time the amount of null or zero values in the columns will differ so I need intelligence in the sql-statement that takes care of this and this is above my knowledge for the moment :-(And of course this is very simplified and I have my reason to include a country that is not represented in the sample data and so on ;-)Here you go with the sample table./****** Object: Table [dbo].[tblTest] Script Date: 2013-03-05 09:58:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblTest]( [DropRate] [decimal](38, 12) NULL, [Country] [nchar](10) NULL, [MarketingName] [nvarchar](500) NULL, [Manufacturer] [nvarchar](200) NULL) ON [PRIMARY]GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.547000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.274000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.476000000000 AS Decimal(38, 12)), N'Finland ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.494000000000 AS Decimal(38, 12)), N'Finland ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(0.000000000000 AS Decimal(38, 12)), N'Lithuania ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.513000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.431000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(2.625000000000 AS Decimal(38, 12)), N'Sweden ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.370000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.384000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GOINSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.337000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')GO |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 05:15:08
|
[code]SELECT *, (ISNULL(Denmark,0) + ISNULL(Finland,0) + ISNULL(Lithuania,0) + ISNULL(Norway,0) + ISNULL(Sweden,0)) /(CASE WHEN ISNULL(Denmark,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Finland,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Lithuania,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Norway,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Sweden,0)>0 THEN 1 ELSE 0 END) AS Total FROM (SELECT DropRate,MarketingName, Manufacturer, Country FROM tblTest) s PIVOT (AVG(DropRate)FOR Country IN ([Denmark], [Finland], [Lithuania], [Norway], [Sweden]))P [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
tota00
Starting Member
10 Posts |
Posted - 2013-03-05 : 05:48:33
|
quote: Originally posted by visakh16
SELECT *, (ISNULL(Denmark,0) + ISNULL(Finland,0) + ISNULL(Lithuania,0) + ISNULL(Norway,0) + ISNULL(Sweden,0)) /(CASE WHEN ISNULL(Denmark,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Finland,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Lithuania,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Norway,0)>0 THEN 1 ELSE 0 END +CASE WHEN ISNULL(Sweden,0)>0 THEN 1 ELSE 0 END) AS Total FROM (SELECT DropRate,MarketingName, Manufacturer, Country FROM tblTest) s PIVOT (AVG(DropRate)FOR Country IN ([Denmark], [Finland], [Lithuania], [Norway], [Sweden]))P ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Great!That solved my problem and you made my day visakh16 ! :-)As I´m building the statement dynamically this was exactly what I was looking for!Many thanks!Tested your solution and it works like a charm,!Thanks again // Thomas |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 06:05:26
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|