what sql statement shoud i use? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

what sql statement shoud i use?

Author

Topic

rad14i
Starting Member

4 Posts

Posted - 2009-11-08 : 20:15:27

hello, i have a table that contains the ff columns:

Item,Sales,Date

now i trying to make a query where i can show the sales per item and per month,, the output should be look like this one

.......JAN FEB MAR APR . . .. .
ITEM1 SALES SALES SALES
ITEM2
ITEM3

this is what i have from now

select item_number,sum(sales_amt) from item_sales where dr_date>='01/01/09' and dr_date<='01/31/09' group by item_number

select sum(sales_amt) from item_sales where dr_date>='02/01/09' and dr_date<='02/28/2009' group by item_number

and i will make another query for the remaining months, what i want is i want to combine the 2 query,, i dont have any idea, i cant use JOINs statements, because i only have one table,please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-09 : 01:48:02

select item_number,sum(sales_amt) from item_sales where dr_date>='01/01/09' and dr_date<='01/31/09' group by item_number
union all
select item_number,sum(sales_amt) from item_sales where dr_date>='02/01/09' and dr_date<='02/28/2009' group by item_number

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 10:54:00

seems like what you need is


SELECT item_number,
sum(case when dr_date>='01/01/09' and dr_date<='01/31/09' then sales_amt else 0 end) as JAN,
sum(case when dr_date>='02/01/09' and dr_date<='02/28/2009' then sales_amt else 0 end) as FEB,
...
FROM item_sales
group by item_number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources