Post reply

Count off when using distinct and group by

  • January 17, 2003 at 4:10 pm

    #80182

    I am trying to compute the percentage of times each minimum amount (minAmt) category occurs. When I run the first statement to get the total records I get one number. Then I sum the "frequency" column and get another number a couple of thousand off. I checked for nulls and none exist for the minAmt column. Any help?

    select count(minAmt) as 'total' from applicantProcessed

    select

    distinct

    minAmt = case

    when minAmt <= 20000 then 1

    when 20000 < minAmt and minAmt <= 35000 then 2

    when 35000 < minAmt and minAmt <= 50000 then 3

    when 50000 < minAmt and minAmt <= 75000 then 4

    when 75000 < minAmt and minAmt <= 100000 then 5

    when 100000 < minAmt and minAmt <= 150000 then 6

    when 150000 < minAmt and minAmt <= 200000 then 7

    when 200000 < minAmt and minAmt <= 300000 then 8

    when 300000 < minAmt and minAmt <= 400000 then 9

    when 400000 < minAmt and minAmt <= 500000 then 10

    when 500000 < minAmt then 11

    else 0 end

    , count(*) as 'Frequency'

    from

    applicantProcessed

    group by

    minAmt

    , minAmt

  • January 17, 2003 at 5:29 pm

    #446691

    Which is smaller?

  • January 17, 2003 at 6:06 pm

    #446696

    DISTINCT in the second query might be causing the difference.

    Edited by - mromm on 01/17/2003 6:06:37 PM

  • January 17, 2003 at 7:03 pm

    #446704

    The sum of the frequency column is smaller.

  • January 17, 2003 at 8:27 pm

    #446709

    Ok, then DISTINCT is finding rows that duplicate in value and is removing them. This is why you are seeing a smaller number.

    Consider a table with a column for char(1) that looked like this

    a

    a

    b

    c

    d

    d

    e

    f

    count(*) would = 8

    but

    DISTINCT count(*) would = 6 because one a and one d are duplicates.

    Check the duplication amounts you have by using HAVING and a column.

    Select COl1, COunt(col1) FROM tblX GROUP BY COL1 HAVING COUNT(COL1) > 1

    Then take the total of those - the number of rows in the resultset and see if that is the difference.

  • January 20, 2003 at 9:56 am

    #446782

    I was shooting for returning distinct values for minAmt and a count of those values as 'frequency' After running some counts as suggested by "Antares686" It looks like I was getting distinct combinations of minAmt and "frequency" Thanks for the help

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

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