Post reply

Count of Distinct Records

  • April 1, 2004 at 1:19 pm

    #84664

    I know I have seen this mentioned before but I can't find any of the discussions so I am asking again.

    I've got a table that has a field, FieldA, that contains people's names.

    FieldA has duplicates.  I want to get a count of the number of DISTINCT entries in FieldA.

    How do I do it?

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • April 1, 2004 at 1:47 pm

    #501515

    You can try this:

    SELECT COUNT(*) FROM (SELECT DISTINCT FieldA FROM TableA) AS A 

  • April 1, 2004 at 2:05 pm

    #501517

    Hey!

    SELECT COUNT( DISTINCT [FieldA] ) FROM Table1

    Hope this helps!

    Kindest Regards,

  • April 1, 2004 at 2:08 pm

    #501518

    Thanks, I thought I had tried it before but I tried it again.  I must have had "fat fingers" the first time.

    Thanks again.

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • April 1, 2004 at 2:16 pm

    #501519

    Hey!

    Also, the method racosta posted, and the method I posted should produce identical query plans... it just depends on how you want to do it!

     

    Kindest Regards,

  • April 1, 2004 at 2:18 pm

    #501521

    Yes, I did both and got the same results.  I admit I had not thought of racosta's method but it works.

    Thanks to both of you.

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • April 2, 2004 at 10:55 am

    #501653

    I everyone,

    Just want to add this:

    The use of this method:

    SELECT COUNT( DISTINCT [FieldA] ) FROM Table1

    Will always work if you have one column. But, of course, won't work if you have multiple columns:

    SELECT COUNT( DISTINCT [FieldA], [FieldB]&nbsp FROM Table1

    The other suggestion will work in all cases:

    SELECT COUNT(*) FROM (SELECT DISTINCT FieldA, FieldB, ... FROM TableA) AS A

    Best Regards,

    Carl

  • April 2, 2004 at 10:57 am

    #501654

    Ouups, s'cuse me for the typo error:

    Should have read:

    SELECT COUNT( DISTINCT [FieldA], [FieldB], ... ) FROM Table1

    Carl

  • April 28, 2008 at 1:30 am

    #807618

    hi all,

    i managed to get a result using the above suggestions,but i am not sure hw to interpret it.first of all, wat i want is,to find out how many times a certain value appears in the result set of a query.for example, i have a table,table1, with 3 columns(column1,column2,column3).my query is:

    Select * from table1 where column1='val'.

    what i wanna do with this result is,to find out how many times a certain value(say 'a') appears in column2, so that i can count the frequency.

    when i change my query to SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table1) AS A,

    i get a number as a result. i think this means there are this number of distinct values in the result.but it still doesnt fulfill my aim.

    any suggestions?

  • April 28, 2008 at 1:45 am

    #807624

    Hi

    Is this what you want...

    SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table1 where column2 = 'a') AS A,

    "Keep Trying"

  • April 28, 2008 at 6:22 pm

    #808093

    hi all,

    thanks chirag for your suggestion. but that code is still giving me a number, which i supposed is the number of distinct entries..

    anyway,i found a way to do it.

    SELECT column1 ,COUNT(column1) AS expr1

    FROM (SELECT * FROM table1 WHERE column2='a')

    GROUP BY column1

    maybe not the most elegant way to do it,but it returns wat i want;)

    any suggestions for improvements are welcomed:)

    thanks!!:D

  • April 29, 2008 at 12:07 am

    #808126

    For starters...

    specify the column names instead of "*".

    SELECT column1 ,COUNT(column1) AS expr1

    FROM (SELECT column1 FROM table1 WHERE column2='a')

    GROUP BY column1

    wont this suffice..

    SELECT column1 ,COUNT(column1) AS expr1 from table1

    where column2 = 'a'

    GROUP BY column1

    "Keep Trying"

  • April 29, 2008 at 9:30 am

    #808479

    Would this be too simple an approach? I think it gets what you are after via Rowcount and could be gotten right after your initial query was done. Sometimes I simplify too much but let me know what you think.

    select * from customers

    where CustomerID Like 'A%'

    select 'Rows are ', @@rowcount

    Thank you

    Toni

  • April 30, 2008 at 2:46 am

    #808863

    as i said dont use * if u can.

    "Keep Trying"

  • April 30, 2008 at 6:37 am

    #808986

    Agreed Chirag, you would not use the '*' (did not mean to imply you would - just lazy on my part to leave it in).

    shwetha004 had stated "i managed to get a result using the above suggestions,but i am not sure hw to interpret it.first of all, wat i want is,to find out how many times a certain value appears in the result set of a query. " In the case he showed:

    .for example, i have a table,table1, with 3 columns(column1,column2,column3).my query is:

    Select * from table1 where column1='val'.

    You would replace the '*' with column1 - columnwhatever: Select column1 from table1 where column1=val.

    My point was if all you wanted was a way to count how many rows were in the result set of that query, @@ROWCOUNT would easily give it without needing to do anymore queries.

    Toni

Viewing 15 posts - 1 through 15 (of 16 total)

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