Post reply

Alternative to using DISTINCT with COUNT Function

  • 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

  • September 19, 2006 at 11:25 am

    #661316

    Can U do Derived Table

    Select count(*)

    From (select distinct QuestionTreeUID FROM tbl_NSP_Answer) T

  • September 19, 2006 at 2:42 pm

    #661366

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







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

  • September 19, 2006 at 4:49 pm

    #661381

    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

  • September 19, 2006 at 5:27 pm

    #661384

    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

  • September 19, 2006 at 7:45 pm

    #661400

    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 !!!**

  • September 20, 2006 at 6:52 am

    #661468

    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 ).

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

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