Post reply

Count of Distinct Records

  • May 1, 2008 at 6:43 am

    #809503

    The key is to use the DISTINCT qualifier on the fields that you want distinct values for.

    For example, I have a list of Customer orders and want to know how many distinct PONumbers I have for each customer, from a detailed list of POItems (where there can be duplicate PO numbers for multiple items):

    SELECT

    COUNT ( DISTINCT PONumber )

    , CustomerNumber

    FROM POItems

    GROUP BY

    CustomerNumber

    returns for me a count of distinct PONumbers for each CustomerNumber. You can also use the DISTINCT qualifier in other functions as well, such as SUM ( DISTINCT TotalPrice ). You just use the DISTINCT qualifier for the fields that you want grouped as a distinct values.

    There is no need to use any additional GROUP qualifier unless you are including other non-aggregrated fields (like I did with CustomerNumber in my example).

    It's pretty cool! 😛

    [font="Verdana"]If technology is supposed to give us more freedom and empower us to pursue the more important things in life, why do so many people allow themselves to become enslaved by it? Always remember, the truly important people cannot be reached... except when they want to reach you.[/font]

  • May 1, 2008 at 11:24 pm

    #809930

    Toni

    Agreed that @@Rowcount will give you the desired result.

    However you have to "select @@Rowcount" after the query .

    This will give 2 result sets , first the result set of the query and next one the count. So you end up manipulating 2 result sets instead of one for the same thing.

    I would like to believe that the count(col1) and @@rowcount are treated the same way inside SQL. However not sure on this point.

    "Keep Trying"

Viewing 2 posts - 16 through 16 (of 16 total)

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