case statement - 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 2000 Forums

Transact-SQL (2000)

case statement

Author

Topic

tup
Starting Member

13 Posts

Posted - 2003-09-02 : 06:54:31

HI,
I have a little problem whith my query and I thought someone would help me out.I wrote this query :

select substring(o.sifra,1,4) Dega,o.sifra [Shifra e nendeges], o.naziv NenDega,
case t.isfirma
when 0 then count(distinct embg)
else 0
end Fizik,
case t.isfirma
when 1 then count(distinct embg)
else 0
end Juridik,
count(distinct embg) Total

from istdev i
join tippart t on i.tip=t.tip
join oj o on i.ojk =o.sifra
where dotvoranje <='31.08.2003'
group by t.isfirma,o.sifra,o.naziv,substring(o.sifra,1,4)
order by o.sifra,t.isfirma

And I get this result:

dega shifra nendega fizik juridik total
---- ------ ------- ------ ------- -----
0000 0000 prishtin 13551 0 13551
0000 0000 prishtin 0 1847 1847
0000 000004 besian 110 0 110
0000 000004 besian 0 5 5
0000 000006 lypjan 32 0 32
0000 000006 lupjan 0 2 2

What I want to do is to exclude zeros , in other words to show me this result

dega shifra nendega fizik juridik total
---- ------ ------- ------ ------- -----
0000 0000 prishtin 13551 1847 15398
0000 000004 besian 110 5 115
0000 000006 lypjan 32 2 34

thanks

dsdeming

479 Posts

Posted - 2003-09-02 : 08:31:52

Have you tried adding

HAVING COUNT( DISTINCT embg ) > 0?

Dennis

tup
Starting Member

13 Posts

Posted - 2003-09-02 : 08:37:31

Yes I have ,but it doesn't work with this query

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-02 : 08:44:14

Try this:

SELECT	SUBSTRING(o.sifra,1,4) Dega
	, o.sifra [Shifra e nendeges]
	, o.naziv NenDega
	, COUNT(DISTINCT (CASE t.isfirma
		WHEN 0 THEN embg ELSE null END)) Fizik
	, COUNT(DISTINCT (CASE t.isfirma
		WHEN 1 THEN embg ELSE null END)) Juridik
	, count(distinct embg) Total 

FROM	istdev i JOIN tippart t ON i.tip=t.tip
	JOIN oj o ON i.ojk =o.sifra
WHERE dotvoranje <='31.08.2003' 
GROUP BY t.isfirma,o.sifra,o.naziv,substring(o.sifra,1,4)
ORDER BY o.sifra,t.isfirma