CODEWITHSUNDEEP
phpmysqlsql
user3611097
user3611097

Reputation: 1

How can count my result on my table in MySQL

I have a table for exam result, how can I filter and count my "correct" values on my table and somehow group them by User Number and Module? for example, i only want to count my "correct" values:

Upvotes: 0

Views: 63

Answers (4)

Raging Bull
Raging Bull

Reputation: 18737

Two alternatives:

  1. Using CASE:

    SELECT UserNo,
       SUM(CASE WHEN QuestionResult = 'Correct' THEN 1 ELSE 0 END) as QuestionResult, 
       ModuleNo as Module
FROM TableName 
GROUP BY `UserNo`,ModuleNo
ORDER BY ModuleNo,UserNo

    Result:

    USERNO  QUESTIONRESULT   MODULE
123456  3                1
987456  2                1
123456  4                2
987456  1                2

    See result in SQL Fiddle.

  2. Using WHERE caluse:

    SELECT UserNo,
       COUNT(QuestionResult) as QuestionResult, 
       ModuleNo as Module
FROM TableName 
WHERE QuestionResult = 'Correct'
GROUP BY `UserNo`,ModuleNo
ORDER BY ModuleNo,UserNo

    See result in SQL Fiddle.

Upvotes: 1

potashin
potashin

Reputation: 44581

First solution using SUM and CASE

SELECT `UserNo` ,
       SUM(CASE WHEN `QuestionResult` = 'Correct' THEN 1 ELSE 0 END) AS QuestionResultCount ,
       `Module`
FROM `TableName`
GROUP BY `UserNo` ,
         `Module`

Second solution using COUNT and WHERE clause : (Note: This could be better for large tables with proper indexes, since the DBE can use that index to filter the table while it can not be done with any CASE..WHEN based solutions.)

SELECT `UserNo` ,
        COUNT(`QuestionResult`) AS QuestionResultCount ,
       `Module`
FROM `TableName`
WHERE `QuestionResult` = 'Correct'
GROUP BY `UserNo` ,
         `Module`

Third solution using COUNT and CASE :

SELECT `UserNo` ,
        COUNT(CASE WHEN `QuestionResult` = 'Correct' THEN `QuestionResult` END) AS   QuestionResultCount ,
       `Module`
FROM `TableName`
GROUP BY `UserNo` ,
          `Module`

SQLFiddle

Upvotes: 1

underscore
underscore

Reputation: 6877

Use COUNT

SELECT UserNo,
       COUNT(QuestionResult)
FROM TABLE
WHERE QuestionResult = 'Correct'
GROUP BY ModuleNo, ModuleNo

Upvotes: -1

Zim84
Zim84

Reputation: 3497

SELECT
UserNo, ModuleNo, COUNT(QuestionResult) as correctanswers
FROM yourtablename
WHERE
QuestionResult='Correct'
GROUP BY
UserNo, ModuleNo

You have to group twice if you want it grouped by the user and the module.

Upvotes: 1

Related Questions