February 8, 2013 at 7:35 am
Hello! I have a query which returns data with the following structure:
CountryYearMonthValue1Value2
Country1 2006January102
Country1 2006February 155
Country1 2007January115
Country1 2008January132
Country2 2007June158
Country2 2008July191
Country2 2008August164
Country3 2006June113
Country3 2007September151
Country4 2006December102
Country4 2007May177
Country5 2006June172
Country5 2007May188
Country6 2008April153
Country6 2008April162
Country7 2006May1010
Country8 2006June55
Some countries can be included in multiple regions, for example:
Region1 = Country1, Country2, Country3
Region2 = Country4, Country5, Country6
Region3 = Country1, Country2, Country3, Country4, Country5, Country6, Country7
What I need to obtain is something with this structure:
Country/Region Total val1Total val2
Country1 4914
Country2 5013
Country3 264
Country4 279
Country5 3510
Country6 315
Country7 1010
Region1 125 31
Region2 9324
Region3 22865
All countries 23370
Any idea/suggestion on what I could use in a SQL query to get the result above?
Thank you!
February 8, 2013 at 8:04 am
Use the SUM aggregate function.
http://msdn.microsoft.com/en-us/library/ms187810.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply