Wahay! - 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)

Wahay!

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-05-19 : 04:52:19

I've just discovered this and thought it might be useful to someone even though it is very simple it had me baffled for a while.

I was trying to get a count of the total number of individual entries in a column and was using

select counseled, count(counseled)
from clinic
group by counseled

which worked fine apart from giving the value 0 for the nulls. After a lot of messing about and tearing of hair I found that what I should have been doing was

select counseled, count(*)
from clinic
group by counseled

which works a treat, however out of curiosity I was wondering if it is possible to count the nulls in a column without using count(*)?

thanks

steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-19 : 08:08:38

This is the same as COUNT(*)

SELECT SUM(1)
FROM
something

No reason to use it instead. Note that you aren't counting COLUMN values, you are counting ROWS, if you ommit the checking for Nulls.

Also look at

SELECT COUNT(Distinct Col1)
FROM
Table

That returns the # of DISTINCT values in a particular column. Read books-on-line for more info.

To count just how many rows are null in a column:

SELECT SUM(CASE WHEN COl1 Is Null THEN 1 ELSE 0 END)
FROM
table

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-19 : 09:00:27

quote:

This is the same as COUNT(*)

SELECT SUM(1)
FROM
something

Actually, it's not.

SELECT COUNT(*)
FROM something
WHERE 1 = 0

SELECT SUM(1)
FROM something
WHERE 1 = 0

yield 0 and NULL respectively.
(cue Joe Celko)

Edited by - Arnold Fribble on 05/19/2003 09:00:53

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-19 : 09:06:27

Arnold -- good point (as always!).

Actually, a better example might be

SELECT COUNT(Any_Non_Null_Constant) <-- like count(1) or count(0)
FROM
something

is the same as

SELECT COUNT(*)
FROM
something

- Jeff

Edited by - jsmith8858 on 05/19/2003 09:06:50

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-19 : 15:51:37

Interesting...wondered what that meant...never used it like that before

SELECT COUNT(1) FROM sysobjects

SELECT COUNT(*) FROM sysobjects

SELECT COUNT(0) FROM sysobjects

SELECT COUNT('A') FROM sysobjects

All return the same, so I asked how come, went to BOL and found:

expression

Is an expression of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted.

*

Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.

Since the literal expressions are the same for every row, it doesn't matter. So if the expression is a column, it won't count the null values.

SELECT Count(keys) FROM sysindexes

SELECT Count(id) FROM sysindexes

SELECT Count(*) FROM sysindexes

quote:

which works a treat, however out of curiosity I was wondering if it is possible to count the nulls in a column without using count(*)?

There happens to be no Null values in id, so the number of null rows would be:

SELECT Count(*)-Count(keys) FROM sysindexes

Which could also be done by:

SELECT Count(*) FROM sysindexes WHERE keys IS NULL

Which doesn't answer you question, but...

Why Do SELECT COUNT(1)?

Brett

8-)

Edited by - x002548 on 05/19/2003 15:56:00

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-19 : 16:55:27

select count(x) should return the same for any non nullable column or consatnt or *.
If you give it a column then it will restrict the query plan, */constant allows use of any index.

select count(*) just looks better than select count(1) after all you are just counting rows rather than the number of 1's.

same as
if exists (select * from
is better than
if exists (select 1 from

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-20 : 14:39:55

quote:

if exists (select * from
is better than
if exists (select 1 from

Really? Why. Isn't SELECT * returning all of the columns in a result set, where as 1 is just an int?

I'll have to play around with some execution plans.

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-20 : 16:53:33

Nope.
SQL server knows about if exists (select (*) ...
and just uses whichever index is best to count rows.

You should find that all databases are optimised for this construct.
I think there was a time when it was better than select 1 but it was a long time ago - it might not have been sql server.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-20 : 16:57:18

Buddy of mine should me the SELECT 1 "trick" in 6.5...Where did you learn that. Is it in BOL?

I think my OS/390 DB2 V7 still reacts that way though...but now I'll have to double check.

Nothing about it in any of the "what's new" section of the manuals.

Thanks Nigel

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources