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 2008 Forums
 Transact-SQL (2008)
 Help with SUM and Group By		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tota00
Starting Member

Sweden
10 Posts
Posted - 01/31/2013 :  07:47:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/31/2013 :  08:31:14  Show Profile  Reply with Quote
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
Go to Top of Page

tota00
Starting Member

Sweden
10 Posts
Posted - 01/31/2013 :  08:38:01  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 01/31/2013 :  08:52:19  Show Profile  Reply with Quote
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/
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/31/2013 :  09:42:34  Show Profile  Reply with Quote
You are very welcome tota00.

Thanks for that link Visakh - very useful!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 01/31/2013 :  09:46:26  Show Profile  Reply with Quote
you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.05 seconds. Powered By: Snitz Forums 2000