MySQL List some field from a single table and COUNT all particular field reference from another table

Question

The subject of my question is a bit confusing because I'm running out of words to suit the general and technical description of it.

In a less technical terms, what I'm trying to accomplish specifically is:

List all churches and their corresponding booking status from a service they are offering with the following fields:

Church id AS id
Church name AS name
Count of pending status from all services they are offering AS pendingCount
Count of fully booked status from all services they are offering AS bookedCount

The structure of associate tables are as follows:

Church

+---------+---------------+
| id(int) | name(varchar) |
+---------+---------------+

Services

+---------+---------------+---------------+
| id(int) | name(varchar) | churchId(int) |
+---------+---------------+---------------+

Bookings

+---------+-----------------+----------------+
| id(int) | status(varchar) | serviceId(int) |
+---------+-----------------+----------------+

What I have come up so far is this, which I'm totally have no idea why it is compiling but produces a negative result:

SELECT
    churches.id,
    churches.name,
    pending.count AS pendingCount,
    booked.count AS bookedCount
FROM
    churches
INNER JOIN
    services
ON
    services.churchId = churches.id
LEFT JOIN
    (SELECT
        COUNT(bookings.id) AS `count`,
        bookings.serviceId
     FROM
        bookings
     WHERE
        bookings.status = 'pending'
     GROUP BY
        bookings.serviceId)
     AS pending
ON
    pending.serviceId = services.id
LEFT JOIN
    (SELECT
        COUNT(bookings.id) AS `count`,
        bookings.serviceId
     FROM
        bookings
     WHERE
        bookings.status = 'fully booked'
     GROUP BY
        bookings.serviceId)
     AS booked
ON
    booked.serviceId = services.id

Sample output

Your question is not too clear to me. Could you include some sample output? I think you should do this even if you accept the answers below. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 26, 2015 at 3:13
Sample data and expected results would help a lot. My answer is untested, it would be nice to verify it and correct it if necessary. — AdamMc331
– AdamMc331, Commented Oct 26, 2015 at 3:13
Thank you for your time reading my question. The example output has been added as requested :) — mr5
– mr5, Commented Oct 26, 2015 at 3:16

AdamMc331 · Accepted Answer · 2015-10-27 01:01:54Z

4

I would use conditional aggregation for this. It is a way to sum a number of rows that meet a certain value. In this case, we can use aggregation for SUM(b.status = 'fullyBooked') and SUM(b.status = 'pending') and group by church id like this:

SELECT c.id, c.name,
    SUM(b.status = 'pending') AS pendingCount,
    SUM(b.status = 'fully booked') AS bookedCount
FROM bookings b
RIGHT JOIN services s ON s.id = b.serviceId
RIGHT JOIN churches c ON c.id = s.churchid
GROUP BY c.id;

To reiterate, by using SUM(condition) and grouping by a certain id value, you are counting the number of times that condition is true for that id value.

EDIT:

I have added a RIGHT OUTER JOIN to this query so that churches without any bookings will be returned as well.

edited Oct 27, 2015 at 1:01

answered Oct 26, 2015 at 3:12

AdamMc331

16.8k12 gold badges79 silver badges134 bronze badges

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

6 Comments

mr5 Over a year ago

Hmm, but it only outputs churches who have a reference from another table, how could I make a default value, like e.g 0 if they're not present?

JRD Over a year ago

May want a left join to show churches that have no bookings.

mr5 Over a year ago

@JRD I have tried to prepend it with LEFT but seems not working though :/

AdamMc331 Over a year ago

@mr5 the reason LEFT did not work in my example is because I have joined the churches table last. My particular query needs a RIGHT outer join to get churches without bookings.

JRD Over a year ago

@McAdam331, Have never seen the SUM(boolean) before, I like. I usually default to a count(if ...) or count(case when ...), but like this one better.

|

JRD · Accepted Answer · 2015-10-26 03:30:34Z

1

select c.id, c.name,
       SUM(b.status = 'pending') pendingCount,
       SUM(b.status = 'fully booked') bookedCount
from   church c
-- Left join here if you want all churches whether or not they offer a service
left join services s on (c.id = s.churchId)
-- Left join here if you want all churches w/ a service, but may have no bookings
left join bookings b on (s.id = b.serviceId)
group by c.id, c.name;

answered Oct 26, 2015 at 3:30

JRD

1,9871 gold badge13 silver badges17 bronze badges

1 Comment

mr5 Over a year ago

I never thought it was this easy. Your SQL statement is less complex than everyone else's answer and satisfies my general need.

Kofi Amparbeng · Accepted Answer · 2015-10-26 03:25:42Z

0

Try

SELECT
    churches.id,
    churches.name,
    count(case when bookings.status = 'pending' then 1 else null end) AS pendingCount,
    count(case when bookings.status = 'fully booked' then 1 else null end) AS bookedCount
FROM
    churches
INNER JOIN
    services
ON
    services.churchId = churches.id
LEFT JOIN bookings on bookings.serviceId=services.id
group by 
    churches.id,
    churches.name,

edited Oct 26, 2015 at 3:25

answered Oct 26, 2015 at 3:14

Kofi Amparbeng

1581 silver badge10 bronze badges

3 Comments

mr5 Over a year ago

Hmm, but it produces an error Unknown column 'bookings.status' in 'field list'?

Kofi Amparbeng Over a year ago

Amended, forgot to join on bookings table

mr5 Over a year ago

Hmm, seems working though but I have replaced INNER LEFT JOIN with LEFT JOIN

Collectives™ on Stack Overflow

MySQL List some field from a single table and COUNT all particular field reference from another table

Church

Services

Bookings

Sample output

3 Answers 3

6 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Church

Services

Bookings

Sample output

3 Answers 3

6 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related