September 25, 2002 at 10:12 am
Hi we need to produce a query that will allow us to group Sales figures.
We have a table full of sales and what we need is this...
1) Sum the Quantity of all Sales Grouped by Account Number and Part number.
2) We need to display the previous 3 months Sales for that Account & Part number in one row...so if there are no sales for that part number to the account in one month we'd naturally show 0.
Initially a coleague created 3 views and tried to join them all together but there were lots of NULL values and it looked messy...
Any idea's?
Thanks,
Clive Strong
September 25, 2002 at 12:10 pm
Possible something like this will work for you.
create table sales (account int, month int, part int, amt money)
insert into sales values(1,6,1,1)
insert into sales values(1,7,1,1)
insert into sales values(1,8,1,1)
insert into sales values(1,9,2,1)
insert into sales values(1,7,1,1)
insert into sales values(1,8,1,1)
insert into sales values(2,6,1,1)
insert into sales values(2,7,1,1)
insert into sales values(2,8,1,1)
insert into sales values(2,9,1,1)
insert into sales values(2,7,1,1)
insert into sales values(2,8,1,1)
select pma.account,pma.month,pma.part , sum(isnull(s.amt,0))
from sales s right outer join
(select * from (select distinct part from sales) p,
(select distinct month from sales) m,
(select distinct account from sales) a) pma
on s.part = pma.part and s.month = pma.month and s.account = pma.account
where pma.month > month(getdate())-3
group by pma.account, pma.month, pma.part
order by pma.account, pma.month, pma.part
drop table sales
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply