SQL query joining issue

Question

I have two tables login_activity and event_details. The structure of the tables is as below:

Login_activity
activity_id   |  student_id   |  login_date  | event_id

event_details
event_id   |  event_name   |  event_date

I need to fetch the event_id and the count of student_id from login_activity that match the event_id.

I tried the below query

select event_id,student_id 
from login_activity 
where login_activity.event_id in (select event_id from event_details);

But I am not able to get distinct event_id and count of student_ids for those. I know there has to be a simple solution, but I can't seem to get it. What am I missing?

Answer 1

You need to use a GROUP BY and COUNT

SELECT l.event_id, count(student_ID)
FROM login_activity l
 INNER JOIN event_details e ON l.event_id = e.event_id
GROUP BY l.event_id

You actually don't need the event_details table, unless you want to pull in a specific event_name or event_date

SELECT l.event_id, count(student_ID)
FROM login_activity l
GROUP BY l.event_id