SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Pivot
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mikesoper
Starting Member

11 Posts

Posted - 11/14/2013 :  04:38:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

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

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/14/2013 :  04:51:38  Show Profile  Reply with Quote
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 - 11/14/2013 :  04:54:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/14/2013 :  05:01:07  Show Profile  Reply with Quote
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

Edited by - bandi on 11/14/2013 05:01:55
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 11/14/2013 :  05:08:28  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/14/2013 :  05:25:59  Show Profile  Reply with Quote
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 - 11/14/2013 :  10:05:13  Show Profile  Reply with Quote
I get "Operand data type nvarchar is invalid for sum...."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/14/2013 :  13:18:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 11/14/2013 :  23:11:00  Show Profile  Reply with Quote
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 - 11/26/2013 :  08:36:57  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/26/2013 :  09:28:50  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 11/26/2013 09:31:58
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 11/26/2013 :  10:09:29  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/27/2013 :  04:55:24  Show Profile  Reply with Quote
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 - 11/27/2013 :  05:05:35  Show Profile  Reply with Quote
Brilliant - can we round to 1 decimal point here as well?
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 11/27/2013 :  05:10:04  Show Profile  Reply with Quote
Worked that last one out myself - thank you!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  05:13:14  Show Profile  Reply with Quote
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

Edited by - visakh16 on 11/27/2013 05:14:15
Go to Top of Page

mikesoper
Starting Member

11 Posts

Posted - 11/27/2013 :  05:16:21  Show Profile  Reply with Quote
Can we round up or down here?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  05:21:05  Show Profile  Reply with Quote
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 - 11/27/2013 :  05:28:33  Show Profile  Reply with Quote
1 decimal place
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  05:30:41  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000