Selecting columns that are not '0'

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

Selecting columns that are not '0'

Author

Topic

drperic
Starting Member

2 Posts

Posted - 2009-04-29 : 03:51:42

Hy, a need help how to select coulmns that are not 0,here is my sql

select D.D ,C.C,L.L,S.S FROM
(select count(lokacija) as D from dbo.Pretraga where lokacija ='A3/2' and pozicija='D' AND artikl is null) as D,
(select count(lokacija) as C from dbo.Pretraga where lokacija ='A3/2' and pozicija='C' AND artikl is null) as C,
(select count(lokacija) as L from dbo.Pretraga where lokacija ='A3/2' and pozicija='L' AND artikl is null) AS L,
(select count(lokacija) as S from dbo.Pretraga where lokacija ='A3/2' and pozicija='S' AND artikl is null) AS S

Results

D C L S
5 4 0 2

now how to get only columns with no 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-29 : 04:01:14

Not sure what do you want but you may change your query to this way . . it is more efficient.


select D = count(case when pozicija = 'D' then pozicija end),
       C = count(case when pozicija = 'C' then pozicija end),
       L = count(case when pozicija = 'L' then pozicija end),
       S = count(case when pozicija = 'S' then pozicija end)
from   dbo.Pretraga 
where  lokacija =   'A3/2'
and    artikl   is  null

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 04:01:39

You query can be simplified to


select 
	sum(case when pozicija='D' then 1 else 0 end) as D ,
	sum(case when pozicija='C' then 1 else 0 end)  as C,
	sum(case when pozicija='L' then 1 else 0 end) AS L,
	sum(case when pozicija='S' then 1 else 0 end) AS S
from dbo.Pretraga  
	where lokacija ='A3/2' and artikl is null

But where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 04:02:12

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-29 : 04:08:34

LOL !

we have no idea what OP wants and offer the same solution instead

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 04:16:09

quote:
Originally posted by khtan

LOL !

we have no idea what OP wants and offer the same solution instead

KH
[spoiler]Time is always against us[/spoiler]

Yes

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-29 : 04:17:56

Hi Madhivanan,

Are u working for ELLARRInfotek at chennai??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

drperic
Starting Member

2 Posts

Posted - 2009-04-29 : 04:38:38

quote:
Originally posted by madhivanan

But where do you want to show data?

ASP.NET GRIDVIEW, is there a way to not shown column "L" or "D" or ... if count(lokacija)= "0"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-29 : 05:05:25

quote:
Originally posted by senthil_nagore

Hi Madhivanan,

Are u working for ELLARRInfotek at chennai??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Why?

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-29 : 05:10:33

Couple of days before i met a same domain person around ur age group!

That why i want confirm both are same!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources