Discrepancy in SQL Counts: Combining Free and Total Places per Channel

Other Questions MySQL
1

I’m trying to create a SQL query that shows both free and total places for each channel. Right now I have two separate queries:

SELECT c.name, COUNT(p.id) AS available
FROM places p
JOIN channels c ON p.channel_id = c.id
WHERE p.status = 'available'
GROUP BY c.id;

SELECT c.name, COUNT(p.id) AS total
FROM places p
JOIN channels c ON p.channel_id = c.id
GROUP BY c.id;

The first query counts available places, while the second counts all places. How can I combine these to get a result like this?

name | available | total
---------------------------
abc  |     2     |   5
xyz  |     1     |   3

Also, if a channel has no available places, it doesn’t show up in the first query. Is there a way to include all channels, even if they have zero available places? Any help would be great!

2

Having dealt with similar SQL challenges, I can suggest an alternative approach using subqueries. This method often proves more efficient for larger datasets:

SELECT c.name,
       COALESCE(a.available, 0) AS available,
       t.total
FROM channels c
LEFT JOIN (
    SELECT channel_id, COUNT(*) AS available
    FROM places
    WHERE status = 'available'
    GROUP BY channel_id
) a ON c.id = a.channel_id
JOIN (
    SELECT channel_id, COUNT(*) AS total
    FROM places
    GROUP BY channel_id
) t ON c.id = t.channel_id;

This query ensures all channels are included, handles zero available places correctly, and typically performs well on larger tables. It’s worth comparing execution times with other solutions to find the best fit for your specific database setup and data volume.

3

I’ve faced a similar issue before, and I found that using a LEFT JOIN along with COALESCE can solve this problem effectively. Here’s a query that should work for you:

SELECT c.name,
       COALESCE(COUNT(CASE WHEN p.status = 'available' THEN 1 END), 0) AS available,
       COUNT(p.id) AS total
FROM channels c
LEFT JOIN places p ON c.id = p.channel_id
GROUP BY c.id, c.name;

This query will include all channels, even those with zero available places. The COALESCE function ensures that channels without any available places show 0 instead of NULL.

I’ve used this approach in several projects, and it’s been reliable. Just make sure your table and column names match exactly. Also, double-check your data types to avoid any conversion issues.

4

hey, i’ve run into this before. try using a LEFT JOIN with a subquery. something like:

SELECT c.name,
       COALESCE(a.available, 0) AS available,
       COUNT(p.id) AS total
FROM channels c
LEFT JOIN (SELECT channel_id, COUNT(*) AS available
           FROM places WHERE status = 'available'
           GROUP BY channel_id) a ON c.id = a.channel_id
LEFT JOIN places p ON c.id = p.channel_id
GROUP BY c.id, c.name, a.available;

this should give you what you want. let me know if it works!