0

When I use this query :

SELECT channels.name, COUNT(places.id) AS 'free' FROM places INNER JOIN channels ON places.channelId = channels.id WHERE state = 'free' GROUP BY channelId;

The result is :

---------------
| name | free |
---------------
|  foo |    1 |
|  bar |    2 |
---------------

This one give the total number of places :

SELECT channels.name, COUNT(places.id) AS 'total' FROM places INNER JOIN channels ON places.channelId = channels.id GROUP BY channelId;

It gives this result :

----------------
| name | total |
----------------
|  foo |     3 |
|  bar |     4 |
----------------

Do you have a solution to have this result ? :

-----------------------
| name | free | total |
-----------------------
|  foo |    1 |     3 |
|  bar |    2 |     4 |
-----------------------

I have an additional problem : on the first query, if there's no place available, the row of the channel will not come up : example of result if there's no place free for the channel foo with the first query.

---------------
| name | free |
---------------
|  bar |    2 |
---------------

Any idea to have this perfect query ? Is it possible ?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Takah
  • 345
  • 3
  • 20

1 Answers1

0

You could use conditional aggregation like

SELECT c.name, 
COUNT(p.id) AS total ,
SUM(state = 'free') AS free -- SUM(CASE WHEN state = 'free' THEN 1 ELSE 0 END)
FROM places  p
INNER JOIN channels c ON p.channelId = c.id 
GROUP BY c.name;

In mysql when an expression is used inside sum(a= b) it will result as a boolean 0/1 so you can get your conditional count using above

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118