count based on filter - 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)

count based on filter

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-06-12 : 03:30:51

Hi

I have this table...


CREATE TABLE [dbo].[Cards](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CardO] [nvarchar](255) NULL,
	[DateAdded] [datetime] NULL

That have data like this



INSERT INTO Cards (CardO, DateAdded) VALUES ('0', '2011-01-01')
INSERT INTO Cards (CardO, DateAdded) VALUES ('145', '2011-04-21')
INSERT INTO Cards (CardO, DateAdded) VALUES ('78', '2011-03-03')
INSERT INTO Cards (CardO, DateAdded) VALUES ('0', '2011-05-08')
INSERT INTO Cards (CardO, DateAdded) VALUES ('0', '2011-05-08')

I want to count the number of CardO that have values = 0 and also count CardO that have values different from 0, so I get a result like this...

NumberofCardOZero = 3
NumberofCardONotZero = 2

How would I do that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-12 : 05:12:45

[code]
SELECT SUM(CASE WHEN CardO=0 THEN 1 ELSE NULL END) AS NumberofCardOZero,
SUM(CASE WHEN CardO<>0 THEN 1 ELSE NULL END) AS NumberofCardONotZero
FROM Cards
[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2011-06-12 : 05:20:41

Worked as a charm, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources