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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Pivot

Author  Topic 

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 04:38:21
Hi,
I have not used the PIVOT function before and need some help:

My data is like this:
CustID CustType CustValue
1 Type1 10
2 Type2 1
3 Type2 99
4 Type1 5

Could you assist me in using pivot to give me

CustType CustCount CustValue(Sum)
Type1 2 15
Type2 2 100


A simple example I hope will assist me in working further with this.

Much appreciated

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 04:49:33
SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CustValue) AS CustValue
FROM TableName
GROUP BY CustType

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 04:51:38
Refer these links for understanding PIVOT
http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/
For dynamic PIVOT:
http://dotnetgalactics.wordpress.com/2009/10/23/using-sql-server-20052008-pivot-on-unknown-number-of-columns-dynamic-pivot/

--
Chandu
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 04:54:35
OK thanks,

I left out a bit:

Each customer is assigned to an area, area 1, area 2, area 3 etc., and it is by area I need to do this aggregation, hence why I thought of PIVOT.

So by area, what count of customer types and sum of customer values.

Thanks
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 05:01:07
Is this?

SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CustValue) AS CustValue
FROM TableName
GROUP BY CustType, area

If NOT, post the sample data and expected output
--
Chandu
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 05:08:28
The other issue is that CustValue is nvarchar type, as some records contain "REMOVED"

Can we also incorporate a CAST or CONVERT?

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 05:25:59
quote:
Originally posted by mikesoper

The other issue is that CustValue is nvarchar type, as some records contain "REMOVED"

Can we also incorporate a CAST or CONVERT?

Thanks


then convert them to NULL first to ignore them from sum calculation.
something like

SELECT CustType , COUNT(CustID ) AS CustCount , SUM(NULLIF(CustValue,'REMOVED')) AS CustValue
FROM TableName
GROUP BY CustType


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-14 : 10:05:13
I get "Operand data type nvarchar is invalid for sum...."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-14 : 13:18:58
quote:
Originally posted by mikesoper

I get "Operand data type nvarchar is invalid for sum...."



CAST it to int then

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-11-14 : 23:11:00
SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CAST(NULLIF(CustValue,'REMOVED') AS INT)) AS CustValue
FROM TableName
GROUP BY CustType

--
Chandu
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-26 : 08:36:57
Hi,
Thanks this seems to be working. One of the reasons I mentionned a Pivot was because I also need to calculate the % of totals for each group, for example:

CustID CustType CustValue % of total
1 Type1 10 8%
2 Type2 1 1%
3 Type2 99 87%
4 Type1 5 4%

How could I acheive this?

Thanks

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-26 : 09:28:50
[code]
SELECT CustType ,CustCount ,CustValue,
CustValue/SUM(CustValue) OVER () AS [% Of Total]
FROM
(
SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CAST(NULLIF(CustValue,'REMOVED') AS INT)) AS CustValue
FROM TableName
GROUP BY CustType
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-26 : 10:09:29
Thanks - I have tried this but the results for [% Of Total] are always 0?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 04:55:24
what about now?

SELECT CustType ,CustCount ,CustValue,
CustValue*100.0/SUM(CustValue) OVER () AS [% Of Total]
FROM
(
SELECT CustType , COUNT(CustID ) AS CustCount , SUM(CAST(NULLIF(CustValue,'REMOVED') AS INT)) AS CustValue
FROM TableName
GROUP BY CustType
)t


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:05:35
Brilliant - can we round to 1 decimal point here as well?
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:10:04
Worked that last one out myself - thank you!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:13:14
cool. you're welcome

just FYI this was the reason for the 0 values

http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:16:21
Can we round up or down here?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:21:05
quote:
Originally posted by mikesoper

Can we round up or down here?


round to how many decinmal places?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 2013-11-27 : 05:28:33
1 decimal place
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 05:30:41
quote:
Originally posted by mikesoper

1 decimal place



use ROUND fucntion for that

http://technet.microsoft.com/en-us/library/ms175003.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
    Next Page

- Advertisement -