CODEWITHSUNDEEP
mysql
tovin
tovin

Reputation: 83

MySQL | Subtract COUNT from SUM with different WHERE clauses

Here is an example table:

quizzes

id    user_id    quiz_id    has_passed    attempts
1     101        201        0             5          
2     101        202        1             1
3     102        201        1             3
4     103        203        1             2

I have two queries that I would like to combine to get the number of failed quiz attempts.

The first query gets the sum of all attempts for each quiz:

SELECT quiz_id, SUM(attempts) as sum
FROM quizzes
GROUP BY quiz_id

The second query gets counts the number of times a quiz has been passed:

SELECT quiz_id, COUNT(*) as count
FROM quizzes
WHERE has_passed =1
GROUP BY quiz_id

I want to subtract the number of passed attempts from the total attempts to get the number of failed attempts grouped by quiz_id.

What query would give me an output that would look something like this?

quiz_id    failed_attempts    
201        7
202        0
203        1

Thanks!

Upvotes: 1

Views: 2455

Answers (4)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Try below

Select quiz_id, (sum(attempts) - count(*)) as failed attempt from quizzes Group by quiz_id

Upvotes: 0

Steve
Steve

Reputation: 3703

I think you're looking for:

SELECT quiz_id, SUM(attempts) AS attempts, SUM(attempts-has_passed) AS failed_attempts
FROM quizzes
GROUP BY quiz_id

Upvotes: 0

ruakh
ruakh

Reputation: 183484

You can write:

SELECT quiz_id,
       SUM(attempts) - COUNT(CASE WHEN has_passed = 1 THEN 1 END)
         AS failed_attempts
  FROM quizzes
 GROUP
    BY quiz_id
;

Upvotes: 1

peterm
peterm

Reputation: 92805

SELECT quiz_id, SUM(attempts - has_passed) failed_attempts
FROM quizzes
GROUP BY quiz_id

Output

| QUIZ_ID | FAILED_ATTEMPTS |
-----------------------------
|     201 |               7 |
|     202 |               0 |
|     203 |               1 |

sqlfiddle

Upvotes: 5

Related Questions