CODEWITHSUNDEEP
mysqlsqllist
Per76
Per76

Reputation: 184

Group and count returned SQL

I have two tables abb and abb_click.

abb contains information which object ident that's active. abb_click contains information from each student_id regarding each ident.

I "simply" want to count the active ident in abb_click for a specific student_id.

The SELECT below seems to count only the active idents, not taking any concern if the ident is in abb_click.

You can see the fiddle here: http://sqlfiddle.com/#!9/b7262/1

The result should be: for the student_id 945 - 2 active idents

Question: how do I "tweak" the SELECT to count the active idents in table abb joined with abb_click?

SELECT t.student_id, number_of_idents
FROM `abb_click` AS t 
  INNER JOIN 
      (SELECT ident, COUNT(ident) as number_of_idents FROM `abb` AS k 
      WHERE k.active = '1' 
      ) AS t3 
    ON t.ident = t3.ident
WHERE t.student_id = '945' 
GROUP BY t.student_id
ORDER BY number_of_idents ASC;

Table abb

bid, ident, active

Table abb_click

kid, ident, student_id, click

Data to table abb

1, 'ma53', 1 
2, 'ma664', 1 
3, 'ma779', 0 
4, 'ma919', 1

Data to table abb_click

1, 'ma53', 945, 'E'
2, 'ma53', 945, 'E'
3, 'ma53', 945, 'C'
4, 'ma664', 945, 'C'
5, 'ma664', 945, 'A'
6, 'ma664', 945, 'E'
7, 'ma779', 945, 'A'

Upvotes: 0

Views: 52

Answers (3)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31153

You can return the distinct values of ident and student_id by grouping them and then joining with abb and doing the counting:

SELECT click.student_id, COUNT(click.ident) as total_abb_clicks
FROM abb
INNER JOIN (select ident, student_id from abb_click group by ident, student_id) click ON click.ident = abb.ident
WHERE abb.active = 1 and click.student_id=945
GROUP BY click.student_id

Upvotes: 1

Camway
Camway

Reputation: 1062

Unless I've miss-understood this should work.

SELECT abb.*, COUNT(abb_click.id) as total_abb_clicks
FROM abb
INNER JOIN abb_click ON abb_click.ident = abb.ident
WHERE abb.active = 1 && abb_click.student_id = 945 
GROUP BY abb.id

-- Edit: Sorry I forgot the student condition. So this should return:

bid, ident, active, total_abb_clicks
1, 'ma53', 1 , 3
2, 'ma664', 1, 3
4, 'ma919', 1, 1

Upvotes: 1

user4570983
user4570983

Reputation:

I believe this should do what you want.

select student_id, COUNT(distinct ac.ident) as active_idents
from abb_click ac
join abb on abb.ident = ac.ident
where abb.active = 1
--and student_id = 945
group by student_id

Upvotes: 1

Related Questions