Alternative to using DISTINCT with COUNT Function

Question

Post reply

Alternative to using DISTINCT with COUNT Function

Trystan Hughes

SSCrazy

Points: 2109
More actions
September 19, 2006 at 11:19 am

#114339

Is there another way to get the same result from the following query. The reason I ask is because I am using SQL Mobile Everywhere, and the DISTINCT keyword can't be used within a COUNT function unfortunately.
SELECT COUNT(DISTINCT QuestionTreeUID) FROM tbl_NSP_Answer;
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Can U do Derived Table

Select count(*)

From (select distinct QuestionTreeUID FROM tbl_NSP_Answer) T

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Or maybe a variable to store the @@ROWCOUNT ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 3

You forgetting people that I am using SQL Mobile Everywhere, so the use of sub-queries (only allowed in IN Clauses) isn't permitted, and @@ROWCOUNT doesn't work

Thats why I need a simple SQL solution, if at all possible

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Simple solution is proper database design.

You suppose to have separate table with unique identfiers for your "QuestionTree"s.

Probably you have, if you reference QuestionTreeUID.

Then you select COUNT(*) from that table WHERE EXISTS (select 1 from tbl_NSP_Answer ....)

Hope EXISTS works in SQL Mobile Everywhere

_____________
Code for TallyGenerator

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

If temp tables are allowed, then you can do...

select distinct(column)

into #table

from table

select count(*) from #table

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

If it comes to worst... create a perm temp table with the spid as clustered index. or maybe a table variable (not that I think you're allowed to do that at this point ).