SIMPLE SELECT QUERY - 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

General SQL Server Forums

New to SQL Server Programming

SIMPLE SELECT QUERY

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 05:46:13

i have a select query. it will list only non-zero valued rows. (by default)

i need all the rows in the result, irrespective of zero or not

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 05:49:37

remove the filter condition which causes 0 values to be eliminated.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-22 : 05:51:59

quote:
Originally posted by krishna_yess

i have a select query. it will list only non-zero valued rows. (by default)

i need all the rows in the result, irrespective of zero or not

thanks

Post the query you used

Madhivanan

Failing to plan is Planning to fail

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 05:55:51

thanks for ur suggestion, but im not finding any conditions like that.
infact it is NULL not '0'
it is a cross-tax summary, created using case statement.
if all the colums have NULL values then it will not list that row.
how can i avoid this

thanks

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 06:00:46

here is the code
it will not list all-zero-rows.


select product,


count(case when DATEDIFF(day, submitdate ,getdate()) between 0 and 7  then 1 else null end) as [>= 0 week & <= 1 week],

count(case when DATEDIFF(day, submitdate ,getdate())  between 8 and 14 then 1 else null end) as [>1 week & <=2 weeks],

count(case when DATEDIFF(day, submitdate ,getdate()) between 15 and 28 then 1 else null end) as [>2 weeks & <=4 weeks],

count(case when DATEDIFF(day, submitdate ,getdate()) >28 then 1 else null end) as [>4 weeks]

from DASHBOARD_EXCEL 
where state in('Submitted','Assigned','Under_Investigation') 
and severity='1. Showstopper'
AND PRODUCT LIKE '%SKYLIFE%'
GROUP BY PRODUCT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:04:02

what about this?

select product,


sum(case when DATEDIFF(day, submitdate ,getdate()) between 0 and 7  then 1 else 0 end) as [>= 0 week & <= 1 week],

sum(case when DATEDIFF(day, submitdate ,getdate())  between 8 and 14 then 1 else 0 end) as [>1 week & <=2 weeks],

sum(case when DATEDIFF(day, submitdate ,getdate()) between 15 and 28 then 1 else 0 end) as [>2 weeks & <=4 weeks],

sum(case when DATEDIFF(day, submitdate ,getdate()) >28 then 1 else 0 end) as [>4 weeks]

from DASHBOARD_EXCEL 
where state in('Submitted','Assigned','Under_Investigation') 
and severity='1. Showstopper'
AND PRODUCT LIKE '%SKYLIFE%'
GROUP BY PRODUCT

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 06:07:04

same thing..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:10:03

quote:
Originally posted by krishna_yess

same thing..

do you mean you get NULLs even when using sum?

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 06:17:04

yes,
it displays 'NULL' if there are no values(even for the entire row).
this is the way i wanted,

but
are both SUM and COUNT same in CASE statement?

if i replace COUNT with SUM it will not cause any problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 06:20:27

quote:
Originally posted by krishna_yess

yes,
it displays 'NULL' if there are no values(even for the entire row).
this is the way i wanted,

but
are both SUM and COUNT same in CASE statement?

if i replace COUNT with SUM it will not cause any problem?

Nope. it wont. But SUM wont return NULL values. it should return 0 or some value >0

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 06:23:51

ok, thanks.

i'll compare both results....

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-22 : 06:49:10

no
sum returns NULL not 0
coutn return 0
just check

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-22 : 07:41:40