SQL COUNT with case - 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 2008 Forums

Transact-SQL (2008)

SQL COUNT with case

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2011-09-21 : 07:05:25

Hi,

Query:

select a.cod_ctc,
mov_mes=count(CASE WHEN month(dat_mov)=9 THEN a.COD_ART ELSE 0 END)
from ESTMOV a
where COD_MOV IN ('06','03')
and ano='2011'
and month(dat_mov)<=9
GROUP by a.COD_CTC

How can return 0 if the case expression is false (month(dat_mov)<>9)

The Query always return the same value

Regards

Pedro

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 07:12:45

[code]select a.cod_ctc,
mov_mes=count(CASE WHEN month(dat_mov)=9 THEN a.COD_ART ELSE NULL END)
from ESTMOV a
where COD_MOV IN ('06','03')
and ano='2011'
and month(dat_mov)<=9
GROUP by a.COD_CTC

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

psfaro
Starting Member

49 Posts

Posted - 2011-09-21 : 07:18:26

Hi visakh16

Perfect!! Tank's

Kristen
Test

22859 Posts

Posted - 2011-09-21 : 08:08:25

Might give you a "Warning Aggregate contains NULLs" - which may disrupt your application (the warning can look like a resultset to an application)

If so you could use:


mov_mes=SUM(CASE WHEN month(dat_mov)=9 THEN 1 ELSE 0 END)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 08:48:56

or if you want to persist with count and avoid warning set ANSI_WARNINGS to OFF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources