CODEWITHSUNDEEP
mysqlsql
cankcimen
cankcimen

Reputation: 5

Fetch how many failure and success did each person (name) have

I have a table buggy, the dummy dataset link can be see here https://github.com/FirzaCank/Project/blob/main/SQL/IFG%20test/Dataset%20Dummy%20no%205.sql

Which contains:

  1. id (INT)
  2. name (VARCHAR)
  3. bug (INT, contains the numbers 0 and 1)

With dataset explanations on 'bug' column are:

I've tried a MySQL query like this:

SELECT name,
    CASE
    WHEN bug = 0 THEN COUNT(bug)
    END AS failure,
    CASE
    WHEN bug = 1 THEN COUNT(bug)
    END AS success
FROM buggy
GROUP BY name;

The desire output is like This, but as far as I've tried in the above syntax it just came out like this

Thank you for the help!

Upvotes: 0

Views: 200

Answers (2)

Akram Elhaddad
Akram Elhaddad

Reputation: 314

this sql will give wanted result

SELECT t.name , SUM(t.failure) as failure , SUM(t.success) as success

from ( SELECT name ,  CASE
WHEN bug < 1 THEN COUNT(bug) ELSE 0  
END AS failure,
CASE
WHEN bug = 1 THEN COUNT(bug) ELSE 0  
END AS success
FROM buggy
GROUP BY name,bug ) t

GROUP BY t.name;

Upvotes: 1

Gerry Ongko
Gerry Ongko

Reputation: 75

You should use SUM instead of Count.

SELECT 
name, 
SUM(IF(bug = 0, 1, 0)) as fault,
SUM(IF(bug = 1, 1, 0)) as success
FROM buggy
GROUP BY name

This counts the number of rows satisfying the conditions inside the IF function.

Upvotes: 1

Related Questions