SQL - get count from one table based on result of query

Question

I have a two tables, one which contains details of events (name,time,etc), and one which contains records of bookings customers have made for each of these events. I want to be able to query the database and get a set of results that contains all the fields in the events table, plus the number of bookings that there have been for each of these events, so something like:

event_name, event_time, total_spaces_available, number_of_bookings

At the moment I'm getting around this by looping through the results in PHP but is it possible to do within SQL?

I want to be able to do something like:

SELECT *, COUNT(SELECT * FROM bookings WHERE event_id=<VARIABLE>) FROM events

But i don't know the syntax

I'm in MySQL

Any help would be much appreciated!

TiiJ7 · Accepted Answer · 2014-01-10 22:49:04Z

3

Possible solution with a correlated subquery:

SELECT e.event_name, e.event_time, e.total_spaces_available, (SELECT count(*) FROM bookings b WHERE b.event_id=e.event_id) AS number_of_bookings FROM events e

Edit: Like mentioned in the comments, this may be less performant than the join solution. Although I believe the latest MySQL converts them to joins anyway.

edited Jan 10, 2014 at 22:49

answered Jan 10, 2014 at 22:26

TiiJ7

3,4225 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

saamorim Over a year ago

This is a bad practice because if you have 1000 events, mysql will probably do 1000 subqueries to the bookings, one for each event. There is no guarantee that the optimization will work and rewrite this to be efficient. Subqueries are not advisable.

Gordon Linoff Over a year ago

@saamorim . . . A correlated subquery may be the most efficient way of executing this query, especially when there is an index on bookings(event_id). If you have performance comparisons that suggest otherwise, I would be interested in hearing about it.

Randy Over a year ago

This should be fine - check the plans.

saamorim · Accepted Answer · 2014-01-10 22:24:01Z

Do a group by

Select event_name, event_time, total_spaces_available, count(1) number_of_bookings from events inner join booking on events.id = bookings.event_id group by event_name, event_time, total_spaces_available

Collectives™ on Stack Overflow

SQL - get count from one table based on result of query

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related