COUNT(expression) - 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)

COUNT(expression)

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-19 : 11:45:15

I've never had a reason to code COUNT(expression). Last night I thought I had the opportunity to count some TRUE conditions, but COUNT didn't like the syntax: COUNT( A = 5). So, back to SUM(CASE ...)

So A = 5 isn't an SQL expression. COUNT requires an expression that results in a value? But then, what's the point if COUNT incriments by 1 no matter what the value of the expression is computed to be?

Who can clear this up?

Confused...

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-19 : 11:54:09

[code]
USE Northwind
GO

CREATE TABLE myTable99(Col1 char(1))
GO

INSERT INTO myTable99(Col1)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'E' UNION ALL
SELECT NULL UNION ALL
SELECT NULL
GO

SELECT COUNT(*) FROM myTable99
SELECT COUNT(Col1) FROM myTable99
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-19 : 11:57:54

I don't get it...what is it you want to do? COUNT(A=5) doesn't make sense...SELECT COUNT(A) FROM table WHERE A = 5 makes more sense but something tells me you knew this allready

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-19 : 12:22:52

BOL cleared it up, I don't know how I missed this...

quote:
Remarks
COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

So counting rows where A = 5 could be written

COUNT ( NullIf(A, 1))

Edit: Er... or not... I'll stick with SUM(CASE WHEN A = 5 THEN 1 ELSE 0 END )

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-07-19 : 12:37:33

quote:
Originally posted by SamC
I'll stick with SUM(CASE WHEN A = 5 THEN 1 ELSE 0 END )

The 'fall through' value for CASE is NULL, so
COUNT(CASE WHEN A = 5 THEN 1 END)
may be more useful -- particularly if you're expected any zero counts, since
SELECT SUM(1)
WHERE 0=1
returns NULL, not 0.

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 13:40:45

I think you've got it Sam, but COUNT() counts the number of NON null entries.

COUNT(*) is a special case and counts the number of rows - even if all the columns in one, or more, of the rows contains NULLs.

COUNT(DISTINCT MyColumn) tells you the number of different, non-null, values that exist in your column/

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources