Simple SQL Problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple SQL Problem

Author

Topic

sagarpbora
Starting Member

12 Posts

Posted - 2007-11-20 : 17:25:26

Hey all,
I am facing a simple problem in writing a sql query.
I am having a sales table, having following schema

cust varchar(30)
prod varchar(30)
date int
mon int
yr int
state varchar(4)
quant longint

I need to write a query which will give me result in following form
----------------------------------
PROD|JAN_TOTAL|FEB_TOTAL|MAR_TOTAL
----------------------------------
this record will be for the year 2006

I have prepared 3 views, each for JAN_TOTAL, FEB_TOTAL, MAR_TOTAL and joined them using 'prod'

VIEWS:

CREATE OR REPLACE VIEW viewjansum AS
SELECT sales.prod, sum(sales.quant) AS jan_total
FROM sales
WHERE sales.mon = 1 AND sales.yr = 2006
GROUP BY sales.prod;

(Same for Feb and Mar total)

Final Query:

select distinct s.prod, s1.jan_total,s2.feb_total,s3.mar_total from sales s, viewjansum s1,viewfebsum s2,viewmarsum s3 where s1.prod=s.prod and s2.prod=s.prod and s3.prod=s.prod

The query executes fine, yielding blank result.

The main problem here is, if any product do not have any sale for the perticular month(suppose JAN) then final result will not display the result of FEB and MAR also. I want to avoid that.
If the product does not have any sale in JAN then sum should be displayed as 0.

Can anybody tell how to do that?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-20 : 17:33:40

You are making it way to complicated. You don’t need the views and unions.


Select
	Jan_Total  = sum(case when yr=2006 and mon=1 then sales.quant else 0 end),
	Feb_Total  = sum(case when yr=2006 and mon=2 then sales.quant else 0 end),
	etc...

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-21 : 01:04:56

or read about PIVOT in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources