NULLs driving me mad - 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)

NULLs driving me mad

Author

Topic

GS1
Starting Member

27 Posts

Posted - 2002-08-15 : 09:27:30

Hi - a newbie question for you!

I am having some headaches trying to run the following sql statement

SELECT a.DIRECTORY_NM, COUNT(b.LISTING_ID) AS Total_Contacts

FROM
INT_AUX_DIRECTORY a LEFT OUTER JOIN
INT_AUX_DIR_LIST b ON
a.DIRECTORY_ID = b.CONTAIN_DIR_ID

GROUP BY a.DIRECTORY_NM

The statement is looking for the total number of people (listing_id is the primary key records in the 'people table') in each 'folder' on the database.

I was hoping that the above statement would give me a list of every 'folder' (Directory_NM) and a count of the number of records (listing_ID) in that folder.

Unfortunately the results set, whilst returning the correct results, only gives me a coun for 542 folders out of the 548 folders listed if I run the following statement

SELECT a.DIRECTORY_NM
FROM
INT_AUX_DIRECTORY a

along with throwing up the following error message

Warning: Null value eliminated from aggregate.

How could I modify my code to get the full list of 'folders' with a count showing the total number of listing_ids in that folder?

Sorry that this post is confusing - i couldn't think of how to word it as my brain has mushed whilst trying to fiigure this out.

Thanks

G

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 09:36:24

Use Count(*) instead of Count(B.Listing_ID).

GS1
Starting Member

27 Posts

Posted - 2002-08-15 : 10:03:24

This has not had the desired effect, I still am missing six rows of results for some reason, and results in the same warning message. It is really doing my head in now - I cannot work out what the problem might be...

Any other Ideas

Thanks :D

G

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 10:08:42

Well, I can't see how this:

SELECT a.DIRECTORY_NM
FROM
INT_AUX_DIRECTORY a

...can throw a "Warning: Null eliminated from aggregate" because there IS NO aggregate function or a GROUP BY being used. If that's the case, then there's some kind of problem with the table.

GS1
Starting Member

27 Posts

Posted - 2002-08-15 : 10:20:27

Sorry - I don't think that my original post was very clear. The error message occurs when the first statement is run, which does include a GROUP BY and a COUNT.

The second statement,

SELECT a.DIRECTORY_NM
FROM
INT_AUX_DIRECTORY a

is what I'm running to check that all of the desired rows are being output by the first statement (the one containing the aggregate)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 10:31:51

Could there be any nulls in DIRECTORY_NM? Or DIRECTORY_ID? That's the only thing I can think of.

GS1
Starting Member

27 Posts

Posted - 2002-08-15 : 11:37:52

Cracked it!

(nearly!)

It wasn't a sql problem, more a quirk in the setup of the database that allows 'folders' with the same name to be created.

By changing the statement to report on The primary key of the table a, instead of the Directory name, I get the right number of rows output.

Strangely I still get the error message along with my results, which is slightly worrying.

The code now looks like

SELECT a.Directory_ID as Directory, COUNT(b.LISTING_ID) AS Total_Contacts

FROM
INT_AUX_DIRECTORY a LEFT OUTER JOIN
INT_AUX_DIR_LIST b ON
a.DIRECTORY_ID = b.CONTAIN_DIR_ID

GROUP BY a.Directory_ID

Well at least it appears to be returning the correct results now!

G

GS1
Starting Member

27 Posts

Posted - 2002-08-15 : 11:43:26

Strangely, using your suggestion of COUNT(*), so that the statement reads

SELECT a.Directory_ID,a.Directory_NM, COUNT(*) AS Total_Contacts

FROM
INT_AUX_DIRECTORY a LEFT OUTER JOIN
INT_AUX_DIR_LIST b ON
a.DIRECTORY_ID = b.CONTAIN_DIR_ID

GROUP BY a.Directory_ID,a.Directory_NM

returns the results without the error message, but any returned row where Total_Contacts should equal 0 is now returned with the value as 1, which is surely incorrect!

If anyone with a better knowledge of sql than me (I suspect that that is almost everyone on the forum!) could explian this, I would be very grateful.

Thanks

G

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 11:55:21

Counting a null returns 0 because it is not a defined value. Count(*) counts a row even if the columns are all null, because the row exists. You can try this example:

SET NOCOUNT ON
CREATE TABLE #duh (id int null)
INSERT INTO #duh DEFAULT VALUES
SELECT Count(*), Count(id) FROM #duh
DROP TABLE #duh

Since ID is null, count(ID) gives zero. For the same reason, with your LEFT JOIN, the missing IDs in INT_AUX_DIR_LIST give a zero count when using Count(B.Listing_ID).

In any case, use the count expression that gives you the results you want, and don't worry about the warning message, it's not an error.

GS1
Starting Member

27 Posts

Posted - 2002-08-16 : 04:00:16

Robvolk - Thank you for your kind help; as you can see, I am very new to this and much appreciate the 'duh' guide to COUNT, which has cleared up my confusion!

Thanks once again

G

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-16 : 08:35:25

Don't take the "duh" personally, I use it for MY brain, not yours...

It took me some time to figure it out too. If you came from an Access background (like I did), Count(*) wasn't an option, so understanding the differences may take a while.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources