Count Distinct not working - 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

SQL Server Development (2000)

Count Distinct not working

Author

Topic

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-16 : 16:40:55

Hello,

I should be getting 11 records inserted with the query below, but I keep getting 14. 3 of these records have the same WIS_ID in the CTY_FC_PROVIDERSRELATE table, so should be counted (added up), but inserted only once using their WIS_ID.

Any help appreciated.

Jim

INSERT GISLOADER.CTY_FC_VACANCY (WIS_ID, OBJECTID, CHILDCOUNT)
SELECT GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID AS WIS_ID,
GISLOADER.CTY_FC_PROVIDERSRELATE.OBJECTID AS OBJECTID,
COUNT(DISTINCT GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID) AS CHILDCOUNT
FROM GISLOADER.CTY_FC_PROVIDERSRELATE
GROUP BY GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID,OBJECTID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-16 : 17:03:06

For those 3 rows with the same WIS_IDs there must be 3 different OBJECTID values since that is what you are grouping by.

Be One with the Optimizer
TG

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-16 : 17:13:31

I see. If I do not want to group by OBJECTID, and thus take that portion of the Group By statement out, I get the error below:

Column 'GISLOADER.CTY_FC_PROVIDERSRELATE.OBJECTID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 17:38:58

remove it from select also if you don't want it.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-16 : 18:28:13

If you need to include it with your INSERT then you need to decide which "version" of 3 objectids you want.

Be One with the Optimizer
TG

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-17 : 10:16:30

Thanks TG. If it's irrelevant which ObjectID I pull across how would I insert say the first ObjectID encountered of the 3 records and then do the counting and inserting?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 10:35:21

there is nothing like first or last in a relation db.
You could use max,min function incase you think its meaningless.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 10:36:58

[code]INSERT GISLOADER.CTY_FC_VACANCY (WIS_ID, OBJECTID, CHILDCOUNT)
SELECT
GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID AS WIS_ID,
max(GISLOADER.CTY_FC_PROVIDERSRELATE.OBJECTID) AS OBJECTID,
COUNT(DISTINCT GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID) AS CHILDCOUNT
FROM
GISLOADER.CTY_FC_PROVIDERSRELATE
GROUP BY GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID [/code]

Or just populate NULL if you don't need it.

[code]INSERT GISLOADER.CTY_FC_VACANCY (WIS_ID, OBJECTID, CHILDCOUNT)
SELECT
GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID AS WIS_ID,
NULL,
COUNT(DISTINCT GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID) AS CHILDCOUNT
FROM
GISLOADER.CTY_FC_PROVIDERSRELATE
GROUP BY GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID [/code]

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-17 : 10:52:52

TG (and Sakets) Thanks, that worked great. I did need to take out the Distinct clause to get the childcount to work, but your "max" did the trick otherwise. (Final statement is below)

Thanks again so much.

DELETE FROM GISLOADER.CTY_FC_VACANCY
INSERT GISLOADER.CTY_FC_VACANCY (WIS_ID, OBJECTID, CHILDCOUNT)
SELECT
GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID AS WIS_ID,
max(GISLOADER.CTY_FC_PROVIDERSRELATE.OBJECTID) AS OBJECTID,
COUNT(GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID) AS CHILDCOUNT
FROM
GISLOADER.CTY_FC_PROVIDERSRELATE
GROUP BY GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 11:12:41

Hope you have a reason for using delete there in your query. If not, TRUNCATE will be faster.

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-17 : 11:51:07

Per your advice, I changed it to Truncate the table. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources