Tom Cruise Tom Cruise - 3 months ago 14
MySQL Question

select count inside select dinstinct

I have a table named

attendance
which has columns
roll
,
class_id
,
status
and
att_date
.

I have another table named
class
which has columns
class_id
and
name
.

I want to select distinct
class_id
and count
number
of
roll
which has
status = 1
where
date="some_date"
and then connect it to class table using
inner join.
and again apply where branch ="Computer science"

But I'm facing some problem.
This is an example of my table attendance:

roll | class_id | status | att_date
abc | 1 | 0 | 19-06-2016
cvb | 2 | 1 | 19-06-2016
nbs | 1 | 1 | 19-06-2016
lkl | 3 | 1 | 19-06-2016
ewq | 3 | 1 | 19-06-2016
dff | 2 | 1 | 19-06-2016
xyz | 2 | 1 | 19-06-2016


This is an example of my table class:

id | name | branch
1 | CS4 | Computer Science
2 | CS5 | Computer Science
3 | CS6 | Mechanical


and I want something like this :

total number of roll with status 1 | class_id | name
1 | 1 | CS4
3 | 2 | CS5
2 | 3 | CS6


Can someone explain me ?

How can I approach with the query ?

Answer

Use a group by within a group by:

select cnt, count(*) as num_status_1,
       group_concat(a.class_id order by a.class_id) as class_ids,
       group_concat(c.name order by a.class_id) as class_names
from (select class_id, count(*) as cnt 
      from attendance
      where status = 1
      group by class_id
     ) a join
     class c
     on a.class_id = c.class_id
group by cnt;

EDIT:

Note: This aggregates by cnt, and you might not want to do that (your results are ambiguous). This might be sufficient:

select cnt,
       a.class_id, c.nameclass_names
from (select class_id, count(*) as cnt 
      from attendance
      where status = 1
      group by class_id
     ) a join
     class c
     on a.class_id = c.id;

Or even:

select c.*,
       (select count(*) from attendance a where a.status = 1 and a.class_id = c.id)
from class c;