January 31, 2013 at 6:24 am
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_ActiveCountryReportYearReportMonthMarketingNameManufacturer
30Finland2012NovemberMTCBA-G-UF4Multi-Tech Systems, Inc
3Finland2012NovemberTreo 680Palm
1Finland2012NovemberTreo 750naPalm
19Finland2012NovemberiPhoneApple
1Finland2012NovemberThinkPad T61 14Lenovo
1Finland2012NovemberCU515LG
3Finland2012NovemberW580Sony Ericsson
105Finland2012NovemberiPhoneApple
165Finland2012NovemberiPhoneApple
36Finland2012NovemberGM400Ubinetics
2Finland2012NovemberN/AN/A
30Sweden2012NovemberMTCBA-G-UF4Multi-Tech Systems, Inc
103Sweden2012NovemberTreo 680Palm
7Sweden2012NovemberTreo 750naPalm
23Sweden2012NovemberiPhoneApple
165Norway2012NovemberiPhoneApple
30Norway2012NovemberMTCBA-G-UF4Multi-Tech Systems, Inc
103Norway2012NovemberTreo 680Palm
7Norway2012NovemberTreo 750naPalm
23Norway2012NovemberiPhone 5Apple
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)
-----------------
MarketingNameManufacturerFinlandSwedenNorwayTotal
MTCBA-G-UF4Multi-Tech Systems, Inc30303090
Treo 680Palm3103103209
Treo 750naPalm17715
iPhoneApple28923165477
ThinkPad T61 14Lenovo1000
CU515LG1001
W580Sony Ericsson10500105
iPhone 5Apple002323
GM400Ubinetics360023
Thanks in advance // TT
[/code]
January 31, 2013 at 6:41 am
Solved!
I got help from another forum and here is the answer that worked like a charm!
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
January 31, 2013 at 6:11 pm
Right! Works until you have more than 3 countries.
I prefer the crosstab query approach myself. Jeff Moden has a couple of nifty articles on the subject:
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Part 2 (second link) describes how you could handle the case when you don't know how many countries may appear in your data. Look for Dynamic Crosstabs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 1, 2013 at 9:56 am
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! 🙂
February 3, 2013 at 5:25 pm
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! 🙂
Sounds like you've got it covered then. Just wanted to make sure.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply