CODEWITHSUNDEEP
mysqlsubqueryleft-joininner-join
mr5
mr5

Reputation: 3580

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

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:

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

enter image description here

Upvotes: 1

Views: 53

Answers (3)

AdamMc331
AdamMc331

Reputation: 16691

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.

Upvotes: 4

JRD
JRD

Reputation: 1987

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;

Upvotes: 1

Kofi Amparbeng
Kofi Amparbeng

Reputation: 158

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,

Upvotes: 0

Related Questions