Count for registered members PHP MYSQL

Question

I need some help with this code of mine. I want to retrieve total counts of registered members for every event they registered in. I hope someone can help me with this. Thank you in advance.

Here's my code:

SELECT *, COUNT(*) as TotalAttendee 
from activities 
join projectcode on activities.projects_id = projectcode.projects_id
join participants on participants.act_id = activities.id
where activities.projects_id='1001' 
order by YEAR(activities.activity_date) desc

This is should be the output

+------------+----------------+
|Event Title |Total Attendees |
+------------+----------------+
|Event 1     | 110            |
+------------+----------------+
|Event 2     | 90             |
+------------+----------------+

Yes I understand. But without knowing the source it is hard to help — Jens, Feb 20 '23 at 08:23
I'll try to explain. In my program, there are activity table and participant table. The output I want to achieve is if my how many participants in every activity. — Rin Fernandez, Feb 20 '23 at 08:30
As you have not included the DDL for your tables or sample data, it is not clear what the relationships are. Suspect you just need to add `group by activities.id` as `Event Title` is probably (?) functionally dependent on `activities.id`. — user1191247, Feb 20 '23 at 08:48
Please add the error you are getting or add the current response, also add an entity relationship diagram to know your data model — Ernesto Alfonso, Feb 20 '23 at 10:40

score 0 · Answer 1 · answered Feb 25 '23 at 18:20

From your question and the explanation in comments; I can understand;

The output you want to achieve is how many participants in every activity.

This can be achieved by group by in mysql.

Also, Use backticks to surround your output column_names

SELECT act.event_title_column as `Event Title`, COUNT(*) as `Total Attendees`
FROM activities act
JOIN projectcode pr ON act.projects_id = pr.projects_id
JOIN participants pts ON pts.act_id = act.id
WHERE act.projects_id = '1001'
GROUP BY act.id
ORDER BY YEAR(act.activity_date) DESC;

Count for registered members PHP MYSQL

1 Answers1