Help with SUM and Group By

[code="plain"]
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

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 (2/1/2013)

Hi Dwain!

I´m dynamicly building my statement and adding and removing countrys from the sql-question on the fly depending on what filteroptions the user has choosen.

I´m for the moment generating this statement with everything between 1 and 22 countries and a table that contains 1250 000 rows and it works perfectly and still haven´t got any performance problems.

If an new country is added or removed from the database it doesn´t matter as the statement is buildt dynamically and I have a cached list of them to loop through.

However the links You mentioned looks very interesting and I´m gonna test the performance by them contra the above solution.

My question above was really simplified by the way and is much more complexed in reality so there are more parameters involved but that one pointed me to an solution that for now works great!

Anyway! Thanks for Your reply and everything that improves my T-SQL skills are welcome so testing the stuff You wrote is gonna be interesting!

Best // Thomas

Ps. Hope that I can contribute in this forum some day but for the moment You fellows are a little(irony) ahead of me! Smile