Help with SUM and Group By - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Help with SUM and Group By

Author

Topic

tota00
Starting Member

10 Posts

Posted - 2013-01-31 : 07:47:30

Hi!

I have a table shown below (just a part of it) and need to aggregate the data as the example under the table.

I can solve it if i just want the total but needs the sql-statement to return the sum for every item and country (zero if it doesn´t exists for that country and so on)

TABLE and data in it
--------------------


Total_Active	Country	ReportYear	ReportMonth	MarketingName	Manufacturer
30		Finland	2012		November	MTCBA-G-UF4	Multi-Tech Systems, Inc
3		Finland	2012		November	Treo 680	Palm
1		Finland	2012		November	Treo 750na	Palm
19		Finland	2012		November	iPhone		Apple
1		Finland	2012		November	ThinkPad T61 14	Lenovo
1		Finland	2012		November	CU515		LG
3		Finland	2012		November	W580		Sony Ericsson
105		Finland	2012		November	iPhone		Apple
165		Finland	2012		November	iPhone		Apple
36		Finland	2012		November	GM400		Ubinetics
2		Finland	2012		November	N/A		N/A
30		Sweden		2012		November	MTCBA-G-UF4	Multi-Tech Systems, Inc
103		Sweden		2012		November	Treo 680	Palm
7		Sweden		2012		November	Treo 750na	Palm
23		Sweden		2012		November	iPhone		Apple
165		Norway		2012		November	iPhone		Apple
30		Norway		2012		November	MTCBA-G-UF4	Multi-Tech Systems, Inc
103		Norway		2012		November	Treo 680	Palm
7		Norway		2012		November	Treo 750na	Palm
23		Norway		2012		November	iPhone 5	Apple

The Output I need from the sql-statement
 is something like this and of course the month has to be november as well... 
and as You see some items exists more than one time like "apple IPhone" in Finland and some exits only in one country etc..
(You get the idéa)
-----------------
MarketingName	Manufacturer			Finland	Sweden		Norway		Total
MTCBA-G-UF4		Multi-Tech Systems, Inc	30		30		30		90
Treo 680		Palm				3		103		103		209
Treo 750na		Palm				1		7		7		15
iPhone			Apple				289		23		165		477
ThinkPad T61 14	Lenovo				1		0		0		0
CU515			LG				1		0		0		1
W580			Sony Ericsson		105		0		0		105
iPhone 5		Apple				0		0		23		23
GM400			Ubinetics			36		0		0		23

Thanks in advance // TT

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 08:31:14

You can use the PIVOT operator like shown below. If you have an unknown number of countries, or if the country names are not known in advance, you would have to use a dynamic query to construct the pivot.

SELECT
	*,
	ISNULL(Finland,0) + ISNULL(SWeden,0) + ISNULL(Norway,0) AS Total
FROM
	(
		SELECT Total_Active,MarketingName, Manufacturer, Country FROM TheTable 
	) s
	PIVOT (SUM(Total_active) FOR Country IN ([Finland],[Sweden],[Norway]))P

tota00
Starting Member

10 Posts

Posted - 2013-01-31 : 08:38:01

That worked like a charm :-)

Thank´s a LOT James!

quote:
Originally posted by James K

You can use the PIVOT operator like shown below. If you have an unknown number of countries, or if the country names are not known in advance, you would have to use a dynamic query to construct the pivot.
SELECT
*,
ISNULL(Finland,0) + ISNULL(SWeden,0) + ISNULL(Norway,0) AS Total
FROM
(
SELECT Total_Active,MarketingName, Manufacturer, Country FROM TheTable
) s
PIVOT (SUM(Total_active) FOR Country IN ([Finland],[Sweden],[Norway]))P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 08:52:19

see dynamic pivotting method here

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 09:42:34

You are very welcome tota00.

Thanks for that link Visakh - very useful!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 09:46:26

you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources