Counting with conditions

BlueGemini · #1 (**permalink**) 06-23-08, 23:10

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

Quote:

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...

Quote:

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?

r937 · #2 (**permalink**) 06-24-08, 07:12

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

buckeye234 · #3 (**permalink**) 06-24-08, 10:13

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;

r937 · #4 (**permalink**) 06-24-08, 15:36

good catch

BlueGemini · #5 (**permalink**) 06-25-08, 06:03

Thank you very much to both of you..

You're really great!

Internet Services

Web Development

Digital Marketing

Consumer Tech