CODEWITHSUNDEEP
mysql
Debraj Chatterjee
Debraj Chatterjee

Reputation: 35

How to get both present and absent students for a year

I need to get the total no. present and absent students in a year from a table attendance: Here is my query:

SELECT if( status = 'P',count(level_id), 0) AS present, 
       if(status='A',count(student_id), 0) AS Absent 
        FROM attendance WHERE level_id = 'L1' AND date LIKE '2016%'

But this returns the total no. of students to either present or absent section.

Upvotes: 2

Views: 1421

Answers (4)

Y. Joy Ch. Singha
Y. Joy Ch. Singha

Reputation: 3252

This will work:

SELECT DISTINCT(student_name), SUM(status = 'P') AS present, SUM(status='A') AS Absent FROM attendance GROUP BY student_name where date LIKE '2016%'

Upvotes: 0

skini82
skini82

Reputation: 436

Return 1 if you need to count th element:

 SELECT SUM(if( status = 'P',1, 0)) AS present, SUM(if(status='A',1, 0)) AS Absent
 FROM attendance WHERE level_id = 'L1' AND date LIKE '2016%'

Upvotes: 1

KarelG
KarelG

Reputation: 5244

The problem is that you misunderstood what COUNT(...) does in the query. If you really want to count based on being absent or present, then you can use SUM(...) with a conditional step inside.

SELECT
    SUM(if(status = 'P', 1, 0)) AS Present
    SUM(if(status = 'A', 1, 0)) AS Absent
FROM
    attendance
WHERE
    level_id = 'L1' AND date LIKE '2016%'

Upvotes: 2

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

Try group by clause like:

SELECT 
 status, 
 count(student_id) as attendance 
FROM attendance 
WHERE level_id = 'L1' 
  AND date LIKE '2016%' 
GROUP BY status;

It will give you the count for each status available in table

Upvotes: 0

Related Questions