Select Count(Distinct Value) returns 1?

The use of the numeric(16, 0) made me suspect that it was data type related. Add a CAST in the COUNT clause to cast it to an INT type.

The use of the numeric(16, 0) made me suspect that it was data type related. Add a CAST in the COUNT clause to cast it to an INT type: Count(Distinct Cast(O_ID as Int)).

Thankyou Turnkey :) – GateKiller Feb 8 '10 at 16:32.

I'm guessing it's because all rows returned share the same value for O_ID. You can do a COUNT(*) or COUNT() on a key that is unique to each row to get the row count.

All returned rows are unique because of the DISTINCT clause, proven by doing the query with COUNT(). – GateKiller Feb 3 '10 at 13:58 Can you provide a short excerpt of the results without the count, just showing a few O_ID? – Turnkey Feb 3 '10 at 14:03 Well, COUNT(DISTINCT ...) will of course count unique non-NULL values, and that's just it.

Don't do distinct, count(*) or count something that is unique across all your rows, WITHOUT a DISTINCT clause. – Håvard S Feb 3 '10 at 14:06 Your right about how COUNT(DISTINCT ...) works and the result should be the same as the row count without the COUNT() function... – GateKiller Feb 3 '10 at 14:35 Thanks for the confirm, just added as an answer. – Turnkey Feb 3 '107 at 14:10.

Remove the DISTINCT and you'll get a count on all rows.

True. But as you can see from the full query, there is a join involved so this would return duplicate ID's. And it doesn't answer the question of why COUNT() is returning 1 when it shouldn't.

– GateKiller Feb 3 '10 at 14:01 Yes, that is puzzling, thanks for posting the additional info. Did you run the exact same query to get the excerpt, just removing the count? – Turnkey Feb 3 '10 at 14:08 Yeah, the excerpt is the exact same query without using the COUNT() function.

Very puzzling indeed! – GateKiller Feb 3 '10 at 14:33 I wonder if this could be data type related. I wonder if you could add a CAST in the COUNT clause to cast it to an INT type to see if that changes anything?

– Turnkey Feb 3 '10 at 14:56 That totally worked "Count(Distinct Cast(O_ID as Int))" :D. Please can you submit that as an answer, I have some rep points for you. – GateKiller Feb 3 '107 at 10:26.

The first query returns one row with the value "1". The second query returns five rows of unique values. – GateKiller Feb 3 '10 at 14:32 @GateKiller: could you please post the structure of the tables?

– Quassnoi Feb 3 '10 at 14:39 What information are you interested in? I'm not sure I would be allowed to post the full table schema + each table has ALOT of columns. – GateKiller Feb 3 '10 at 14:47 @GateKiller: Just post the relevant columns: O_ID, E_START, E_EnrolmentEmployer: their datatypes, indexes if any and which tables they belong to.

Also, it would be nice to see the execution plans for each query. Just run SET SHOWPLAN_TEXT ON \n GO \n SELECT … (\n is a newline) – Quassnoi Feb 3 '10 at 14:50.

SELECT COUNT(*) FROM vEmployers INNER JOIN vEnrolment ON O_ID = E_EnrolmentEmployer WHERE E_START >= '01-AUG-2008' AND E_START = '01-AUG-2007' ) GROUP BY O_Id.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.