April 2, 2012 at 11:24 pm
Hello...
I have following records....
CompanyID.......Quantity......Sales Year
123.................12..............2011-12
123.................34..............2011-12
123.................12..............2011-12
123.................15..............2012-13
123.................12..............2012-13
123.................12..............2012-13
Now, I want to write a view so I can get the following output
with sum of Quantities with separate sales year...
CompanyID.........Quantity.......Sales Year
123.......................46.................2011-12
123......................12.................2011-12
123......................15.................2012-13
123......................24.................2012-13
I have tried to create a view but it give only 2 records....I want to a view which can have separate records for sales year....
April 3, 2012 at 12:04 am
Do you require sum of quanity on sale year? in your example output you have two sale year rows..
You can try the query:
select customerid,sum(quantity) quantity, saleyear
from tbl
group by customerid, saleyear
If there is any space in saleyear you can trim it
select customerid,sum(quantity) quantity, ltrim(rtirm(saleyear)) saleyear
from tbl
group by customerid, ltrim(rtirm(saleyear))
--Divya
April 3, 2012 at 12:07 am
nilesh-652355 (4/2/2012)
Hello...I have following records....
CompanyID.......Quantity......Sales Year
123.................12..............2011-12
123.................34..............2011-12
123.................12..............2011-12
123.................15..............2012-13
123.................12..............2012-13
123.................12..............2012-13
Now, I want to write a view so I can get the following output
with sum of Quantities with separate sales year...
CompanyID.........Quantity.......Sales Year
123.......................46.................2011-12
123......................12.................2011-12
123......................15.................2012-13
123......................24.................2012-13
I have tried to create a view but it give only 2 records....I want to a view which can have separate records for sales year....
You have only two distinct values for Sales Year, so what do you expect?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2012 at 2:06 am
Koen Verbeeck (4/3/2012)
nilesh-652355 (4/2/2012)
Hello...I have following records....
CompanyID.......Quantity......Sales Year
123.................12..............2011-12
123.................34..............2011-12
123.................12..............2011-12
123.................15..............2012-13
123.................12..............2012-13
123.................12..............2012-13
Now, I want to write a view so I can get the following output
with sum of Quantities with separate sales year...
CompanyID.........Quantity.......Sales Year
123.......................46.................2011-12
123......................12.................2011-12
123......................15.................2012-13
123......................24.................2012-13
I have tried to create a view but it give only 2 records....I want to a view which can have separate records for sales year....
You have only two distinct values for Sales Year, so what do you expect?
I think you want the sum of quanity year wise..so if the sale year is 2011-12 it means you have 2011 and 2012 year data and you want an independent sum of quantity for both the years. For this to be achieved, do you have any other column having date value attached as without that it will be difficult to get the quanity for a single year out of the two years in sales year.
--Divya
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply