Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More help wit Sum, Pivot and so on...


More help wit Sum, Pivot and so on...

Author
Message
tota00
tota00
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
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]))P

The simplified table looks like this:

DropRate      Country      MarketingName      Manufacturer
0.000000000000   Denmark    Zoarmon         Intel
0.000000000000   Denmark    USB316         Sierra Wireless
0.000000000000   Denmark    XP3300-AR1 (P25C005AA)   Sonim
0.000000000000   Denmark    700C         Intermec Technologies Corp.
4.762000000000   Denmark    R6230GE         BlackBerry
0.000000000000   Denmark    SGH-S300      Samsung
0.000000000000   Denmark    Treo 600      Palm


The 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   Total
5130 XpressMusic   Nokia      153.725000000000   NULL   NULL      NULL   NULL   153.725000000000
3310         Nokia      151.236000000000   NULL   NULL      NULL   NULL   151.236000000000
1100         Nokia      151.206000000000   NULL   NULL      NULL   NULL   151.206000000000
iPhone 4      Apple      144.371000000000   NULL   NULL      NULL   NULL   144.371000000000
1208         Nokia      133.742000000000   NULL   NULL      NULL   NULL   133.742000000000
iPhone 4S      Apple      130.181000000000   NULL   NULL      NULL   NULL   130.181000000000
X1-01         Nokia      129.931000000000   NULL   NULL      NULL   NULL   129.931000000000
SGH-E250      Samsung      118.778000000000   NULL   NULL      NULL   NULL   118.778000000000
6300         Nokia      112.905000000000   NULL   NULL      NULL   NULL   112.905000000000
101, 1010      Nokia      110.319000000000   NULL   NULL      NULL   NULL   110.319000000000

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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 19457
Please follow the link in my signature to get details of how to post sample DDL, data and desired results to get the best response to your question.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
tota00
tota00
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
Phil Parkin (3/4/2013)
Please follow the link in my signature to get details of how to post sample DDL, data and desired results to get the best response to your question.


I´m sorry and will post a script for generating a table containing testdata! Sorry again!

// TT
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8310 Visits: 19457
tota00 (3/4/2013)
Phil Parkin (3/4/2013)
Please follow the link in my signature to get details of how to post sample DDL, data and desired results to get the best response to your question.


I´m sorry and will post a script for generating a table containing testdata! Sorry again!

// TT


You're pretty new here, so no need for apologies.

You will find that you get far more responses - with working and tested code - if you take the time to post your question in such a way.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4249 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
tota00
tota00
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18

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: "snip"

SELECT Country, Manufacturer, MarketingName, AvgDropRate=AVG(DropRate)
FROM @Phones
GROUP BY Country, Manufacturer, MarketingName


Thank´s dwain! Will start with this :-)

Best // Thomas
tota00
tota00
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
Okey!
I have come a bit 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   Total
iPhone 3G   Apple   1.408000000000   1.485000000000   0.000000000000   NULL   2.625000000000   5.518000000000

What 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   Total
iPhone 3G   Apple   1.408000000000   1.485000000000   0.000000000000   NULL   2.625000000000   1.839333333333

But 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTest](
   [DropRate] [decimal](38, 12) NULL,
   [Country] [nchar](10) NULL,
   [MarketingName] [nvarchar](500) NULL,
   [Manufacturer] [nvarchar](200) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.547000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.274000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.476000000000 AS Decimal(38, 12)), N'Finland ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.494000000000 AS Decimal(38, 12)), N'Finland ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(0.000000000000 AS Decimal(38, 12)), N'Lithuania ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.513000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.431000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(2.625000000000 AS Decimal(38, 12)), N'Sweden ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.370000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.384000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO
INSERT [dbo].[tblTest] ([DropRate], [Country], [MarketingName], [Manufacturer]) VALUES (CAST(1.337000000000 AS Decimal(38, 12)), N'Denmark ', N'iPhone 3G', N'Apple')
GO


tota00
tota00
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
Problem solved! :-)

Here is the sql-statement that did the trick!

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

Many thanks to Visakh Murukes for the help!

Best // Thomas
John_P
John_P
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 63
Looking at Thomas' select statement made me think of one of my favorite functions, that (IMHO) doesn't get enough use: SIGN

Now, this only works here because there are no negative numbers for each of the countries. SIGN returns one of four values:
SIGN(X) = -1 when X < 0
SIGN(X) = 0 when X = 0
SIGN(X) = 1 when X = 1
SIGN(NULL) = NULL

The bottom part of the average calculation:
(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)

Can be replace with the following:
(SIGN(ISNULL(Denmark,0)) +SIGN(ISNULL(Finland,0)) + SIGN(ISNULL(Lithuania,0)) + SIGN(ISNULL(Norway,0)) + SIGN(ISNULL(Sweden,0))

Good Luck! John
tota00
tota00
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 18
John_P (3/5/2013)
Looking at Thomas' select statement made me think of one of my favorite functions, that (IMHO) doesn't get enough use: SIGN

Now, this only works here because there are no negative numbers for each of the countries. SIGN returns one of four values:
SIGN(X) = -1 when X < 0
SIGN(X) = 0 when X = 0
SIGN(X) = 1 when X = 1
SIGN(NULL) = NULL

The bottom part of the average calculation:
(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)

Can be replace with the following:
(SIGN(ISNULL(Denmark,0)) +SIGN(ISNULL(Finland,0)) + SIGN(ISNULL(Lithuania,0)) + SIGN(ISNULL(Norway,0)) + SIGN(ISNULL(Sweden,0))

Good Luck! John


Thank you John!
This was interesting.
By the way my solution above had to be changed a little bit to take care of division by zero problem (when all droprates in countries is null or equal to zero) but handled that by adding a -0.0000001 dummy value to the division operator.
As I only output a rounded 6 decimal value to the user it doesn´t affect anything.
(ugly workaround I know, but easy ;-(

Thank´s again! // Thomas
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search