Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Red face Unanswered: Counting with conditions

    Can somebody please help me with this. I can't seem to get on how to output this... (if possible, that is)

    pls refer here:
    TABLE1
    jobid personnel errortype
    0001 dave TYPO
    0001 dave TYPO
    0001 dave ME
    0003 justin IBM
    0004 craig TYPO
    0004 craig IBM
    I should display the number of errortypes of every personnel from that particular jobid. So i did this one...

    SELECT
    COUNT (DISTINCT jobid) AS Jobs,
    personnel,
    SUM(errortype) AS Total_Error
    FROM table

    GROUP BY personnel
    Well, if it is just counting and adding the errortypes, I think I got that right but this is the conditions given to me...

    - If the certain jobid has multiple errortypes (refer to jobid 0001), it should always count as just 1
    - If the certain jobid has an IBM errortype, it will equivalently count as 3 already
    - And if the certain jobid has an IBM errortype and other kinds of errortype (refer to jobid 0004), it should only count just the IBM errortype ignoring other errortypes of that jobid, so the error count will be just 3

    What do you think?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT personnel
         , CASE WHEN COUNT(DISTINCT errortype) > 1
                THEN 1
                WHEN SUM(CASE WHEN errortype = 'IBM'
                              THEN 1 ELSE 0 END) > 0
                THEN 3 END
             AS Total_Error
      FROM table
    GROUP 
        BY personnel
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    It looks like the response is close but doesn't work correctly for jobid = 0004, or if a new jobid is added and only has one entry that does not have an errortype of IBM.

    If you do the test for errortype = 'IBM' first and then check the remainder for any a count of >= 1, then I think you'll get what you want.

    Code:
    SELECT personnel,
       CASE WHEN SUM(CASE WHEN errortype = 'IBM'
                          THEN 1 ELSE 0 END) > 0
            THEN 3
            WHEN COUNT(DISTINCT errortype) >= 1
            THEN 1 END
        as Total_Error
    FROM table1
    GROUP BY personnel;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good catch
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2008
    Posts
    49
    Thank you very much to both of you..

    You're really great!

Posting Permissions

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