CODEWITHSUNDEEP
mysqlsql
Mike Ross
Mike Ross

Reputation: 2972

Get count for each record mysql

I have 2 tables name event and checkin It is joined with event.id to checkin.event_id i want to get count of number of checkins in checkin for all records in event

So far i have done this and it returns the value but it returns the count for the entries in checkin table

I want to get no of checkins of each event.id

How do i get that I am using this query right now 

SELECT e.id,COUNT(*) from checkin c 
LEFT JOIN event e ON (c.event_id=e.id) 
GROUP by e.id

which is giving me result like this

event_id    COUNT(*)
1             2
5             5
7             8

Which is fine but i want result like this 

event_id    COUNT(*)
1             2
2             0
3             0
4             0
5             5
6             0
7             8

Hope my question is clear now thank you

Upvotes: 1

Views: 104

Answers (3)

shawnt00
shawnt00

Reputation: 17915

Reverse the join and count non-null event_id:

SELECT e.d, COUNT(c.event_id) AS check_count
FROM event e LEFT OUTER JOIN checkin c ON c.event_id = e.id
GROUP BY e.event_id

Try adding COUNT(e.id) so you can compare it to COUNT(*) or COUNT(c.event_id).

Upvotes: 1

Sourabh Kumar Sharma
Sourabh Kumar Sharma

Reputation: 2807

use the below query with an alias for the counter 

SELECT e.id,COUNT(c.*) as check_count from checkin c 
LEFT JOIN event e ON (c.event_id=e.id) 
GROUP by e.id

Also i recommend you to use PDO or mysqli_* functions instead of mysql_* functions that are deprecated

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

You can do simply as-

select event_id, count(id) 
from checkin group by event_id;

If due to some specific reason you want to cross check that event table should have corresponding rows then you can do as per below-

SELECT e.id, COUNT(c.id) 
FROM event AS e
JOIN checkin AS c ON c.event_id=e.id 
GROUP BY e.id;

If you want to show event id even does not exist in checkin table then you can do as per below-

SELECT e.id, COUNT(c.id) 
FROM event AS e
LEFT JOIN checkin AS c ON c.event_id=e.id 
GROUP BY e.id;

Upvotes: 0

Related Questions