CODEWITHSUNDEEP
mysqlsqlselectcount
user3660378
user3660378

Reputation:

Mysql Count : only showing one result

I want to know what is the right query in counting data in a MySQL database.

for example I want to count the data that has a remark of absent. so the result should be.

|stud_name    |total_absent|
|John Bandola |13          |
|Jeff Oliveras|10          |
|Wendy Lizardo|5           |

etc. etc.

but the problem is, it only shows 1 result which is like this

|stud_name    |total_absent|
|John Bandola |13          |

here is my query 

SELECT 
    stud_name, 
    COUNT(remark) as total_absent 
FROM tb_attendance 
WHERE instructor_id = 'INST-20131296' 
    AND subj_code = 'C100' 
    AND description = 'ADBMS' 
    AND remark = 'Absent'

Upvotes: 2

Views: 504

Answers (3)

Vasim Shaikh
Vasim Shaikh

Reputation: 4532

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.When you talking about collection, 

SELECT 
    stud_name, 
    COUNT(remark) as total_absent 
FROM tb_attendance 
WHERE instructor_id = 'INST-20131296' 
    AND subj_code = 'C100' 
    AND description = 'ADBMS' 
    AND remark = 'Absent' Group By stud_name;

Upvotes: 0

Ujjwal Kumar Gupta
Ujjwal Kumar Gupta

Reputation: 2376

select count(*) from tb_attendance where remark='Absent'

Upvotes: 0

Mureinik
Mureinik

Reputation: 311823

As you need an aggregate result per stud_name, you'd need to group by that field:

SELECT 
    stud_name, 
    COUNT(remark) as total_absent 
FROM tb_attendance 
WHERE instructor_id = 'INST-20131296' 
    AND subj_code = 'C100' 
    AND description = 'ADBMS' 
    AND remark = 'Absent'
GROUP BY `stud_name` -- here

Upvotes: 1

Related Questions