i'm new here! i want to ask my query syntax.

I have a table buggy, the dummy dataset link can be see here https://github.com/FirzaCank/Project/blob/main/SQL/IFG test/Dataset Dummy no 5.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 :

• 0, it means fault / failure
• 1, it means success
• If there is no 'fault', then the 'fault' value will be filled with '0' (null is okay too), so is 'success'

I've tried mysql query like this :

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 helps!

CodePudding user response：

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.

CodePudding user response：

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;