Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Why isn't my DISTINCT working when I use COUNT CASE?

    Why does my DISTINCT no longer work when I try to roll up the results using Count(Case)?

    When I do not use Count(Case), I get the the correct results. However, when I try to roll up the results, the query counts multiple rows that should be kicked out (an employee can have multiple "function", hence the need for the distinct)



    SELECT DISTINCT function, status,
    count(case when months_between(sysdate,birthdate)/12 < 40
    then 1 else null end) as underForty,

    count(case when months_between(sysdate,birthdate)/12 BETWEEN 40 and 49.999
    then 1 else null end) as fortyTo49HRPY

    FROM myTable

    GROUP BY function, status
    ORDER BY function

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    supply the error dood.
    from first look you have empty lines in your code (unless that was just a bad paste).

    Either way post the code you entered in sqlplus and the error all in one.
    Last edited by The_Duck; 01-31-06 at 12:30.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    change the count to sum and the null to 0

    I don't know what purpose the distinct is serving.

  4. #4
    Join Date
    Jan 2004
    Posts
    83
    I have distinct because there are employees who are in the database twice. I only want to count the employees once. But when COUNT is used, it ignores the distinct and counts the duplicate entries.

    There is no error. Just the counts are off because distinct is not working

  5. #5
    Join Date
    Jan 2004
    Posts
    83
    Quote Originally Posted by rbackmann
    change the count to sum and the null to 0

    I don't know what purpose the distinct is serving.
    Same results.

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Hard to figure out when you don't provide any data, but what about moving the distinct inside the count before the case?

    For example
    Code:
    select distinct count(object_type)
      from all_objects
    is different than:
    Code:
    select count(distinct object_type)
      from all_objects
    If this suggestion does not work, save everyone from guessing and post some actual data, the result you are getting, and the result you want!
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    The query has know way of knowing that you only want one entry per employee.

    Distinct retruns distinct values of the result set.

    I think that you need to use analytics to do this



    or you could use a subselect in your from clause.

    select stuff
    count(case casestuff)
    from (select employee, stuff from table group by employee, stuff)
    group by stuff

  8. #8
    Join Date
    Jan 2004
    Posts
    83
    These are the counts I get

    FUNCTION UNDERFORTY FORTYTO49
    Academic 248 288

    This is what I should be getting

    FUNCTION UNDERFORTY FORTYTO49
    Academic 248 268

    "UnderForty" count is correct b/c there are 248 unique employees under the age of 40.

    However, "FortyTo49" is inflated by 10. There are 268 unique employees between 40 and 49, however, 20 employees have two Academic jobs.

    Therefore I want to only count unique employees, disregarding multiple jobs.

  9. #9
    Join Date
    Jan 2004
    Posts
    83
    Quote Originally Posted by rbackmann
    The query has know way of knowing that you only want one entry per employee.

    Distinct retruns distinct values of the result set.

    or you could use a subselect in your from clause.

    select stuff
    count(case casestuff)
    from (select employee, stuff from table group by employee, stuff)
    group by stuff
    This is what I needed. Thanks rbackmann! you were very helpful and kind.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •