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.
Author |
Topic |
Marzagao
Starting Member
6 Posts |
Posted - 2013-07-16 : 13:17:13
|
HiI'm quite new to SQL so learning as I go.I have a sales table populated with the following columns: service_country_code, month, month_id, revenue, product, service_channel, year, month_num,I would like to be able to get a table with: service_country_code, month, month_id, revenue, product, service_channel, year, month_num, quarter_id, quarter_revenuewhere month_num is the month from 1-12where the quarter_id is the quarter from 1-4where the quarter _revenue is the revenue for that quarter.I was able to get the quarter revenue working on the following select code:SELECT service_country_code, month, month_id, revenue, //monthly revenue product, service_channel, year, month_num, CASE WHEN month_num between 1 and 3 THEN 1 WHEN month_num between 4 and 6 THEN 2 WHEN month_num between 7 and 9 THEN 3 WHEN month_num between 10 and 12 THEN 4 END quarter_id FROM ( SELECT service_country_code, month, month_id, product, revenue, service_channel, year, CASE WHEN month='January' THEN 1 WHEN month='February' THEN 2 WHEN month='March' THEN 3 WHEN month='April' THEN 4 WHEN month='May' THEN 5 WHEN month='June' THEN 6 WHEN month='July' THEN 7 WHEN month='August' THEN 8 WHEN month='September' THEN 9 WHEN month='October' THEN 10 WHEN month='November' THEN 11 WHEN month='December' THEN 12 END month_num FROM pmarzagao_test1 )Which I understand may not be the sharpest one for sure (am just starting). However I cant seem to create a table with both the monthly and the quarter revenue for each record..Can you help?Thanks!! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-16 : 13:34:12
|
add the below to select list and checkSUM(revenue) OVER (PARTITION BY [year],[month]) AS MonthlyRevenue,SUM(revenue) OVER (PARTITION BY [year],[quarter]) AS QuarterRevenue ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-07-16 : 18:36:07
|
Is this what you are looking for?[CODE]SELECT service_country_code, [month], month_id, revenue, product, service_channel, [year], month_num, quarter_id, SUM(revenue) OVER (PARTITION BY [year],[quarter_id]) AS QuarterRevenue FROM ( SELECT service_country_code, [month], month_id, product, revenue, service_channel, [year], (CASE WHEN month='January' THEN 1 WHEN month='February' THEN 2 WHEN month='March' THEN 3 WHEN month='April' THEN 4 WHEN month='May' THEN 5 WHEN month='June' THEN 6 WHEN month='July' THEN 7 WHEN month='August' THEN 8 WHEN month='September' THEN 9 WHEN month='October' THEN 10 WHEN month='November' THEN 11 WHEN month='December' THEN 12 END) as month_num, (CASE WHEN [month] in ('January', 'February', 'March') THEN 1 WHEN [month] in ('April', 'May', 'June') THEN 2 WHEN [month] in ('July', 'August', 'September') THEN 3 WHEN [month] in ('October', 'November', 'December') THEN 4 END) as quarter_id FROM pmarzagao_test1 ) A[/CODE] |
|
|
Marzagao
Starting Member
6 Posts |
Posted - 2013-07-17 : 04:21:23
|
Thanks MuMu88 and visakh16When I follow MuMu88 suggestion I get the following:https://www.dropbox.com/s/m69a4v375j7j8e9/Screen%20Shot%202013-07-17%20at%2009.16.14.pngThis is shows the quarterly revenue column but all values are the same for every row. Maybe I was not detailed enough but I wanted, for example, the 2nd-3rd-4th row's monthly revenue to be summed up quarterly. So this would be respecting 'service_country_code', 'product' and 'service channel'.Thanks once more! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-07-17 : 04:28:18
|
add the following in red to your queryover (partition by service_country_code, [year] ... KH[spoiler]Time is always against us[/spoiler] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-17 : 04:43:25
|
quote: Originally posted by Marzagao Thanks MuMu88 and visakh16When I follow MuMu88 suggestion I get the following:https://www.dropbox.com/s/m69a4v375j7j8e9/Screen%20Shot%202013-07-17%20at%2009.16.14.pngThis is shows the quarterly revenue column but all values are the same for every row. Maybe I was not detailed enough but I wanted, for example, the 2nd-3rd-4th row's monthly revenue to be summed up quarterly. So this would be respecting 'service_country_code', 'product' and 'service channel'.Thanks once more!
you need to add the required columns in partition by based on which you want to do the grouping------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Marzagao
Starting Member
6 Posts |
Posted - 2013-07-17 : 04:49:07
|
Thanks a lot khtan and visakh16I learned how to user over and partition now :)Great help! |
|
|
|
|
|
|
|