CODEWITHSUNDEEP
mysqlsql
KJF
KJF

Reputation: 2103

Count across two tables

I have a mysql database with two tables, bookings and users. They are linked via a foreign key user_id in the bookings table.

How would I go about showing the number of bookings per user.

I've been trying variations on this query:

select first_name, last_name, count(*) 
from bookings 
left join users on bookings.user_id 
group by user_id 
order by count(*);

But it's locking the database for an incredibly long time.

Upvotes: 0

Views: 123

Answers (2)

MPelletier
MPelletier

Reputation: 16709

First, your ON clause is not complete. Usually it will be ON X=Y, you have ON X. THis is important because you are telling the database how to link your two tables. I'm guessing this is where the most time is lost.

Second, the LEFT JOIN as used here is useless (unless you want the bookings that have no users.) Try an INNER JOIN.

Third, as a general rule, it's better to do SmallTable INNER JOIN BigTable (not always, your mileage may vary.)

Fourth, as mentioned, use an alias for the ORDER BY clause.

Try:

select first_name, last_name, count(*) as cnt
from users
inner join bookings on bookings.user_id = user.id
group by user_id
order by cnt;

Upvotes: 1

Miguel Prz
Miguel Prz

Reputation: 13792

Select group by in the books:

select user_id, count(1) from bookings group by user_id;

And then if you need the name, etc, join this sub-select with the users table

Upvotes: 1

Related Questions