MySQL: Using COUNT function inside a SELECT statement

Question

I am fairly new to MySQL queries, especially more complex ones. I have two tables that I want to join together (x and y). Table x contains Name, ID and TotalNum where y contains ID and Online. So it looks like this:

Table X

Name  | Store| TotalNum 
Blah  | 1    | 3 
Blah1 | 2    | 2 Etc..

Table Y

Store| Lane  | Online 
1    | 1     | 1 
1    | 2     | 1 
1    | 3     | 0 
2    | 1     | 1 
2    | 2     | 0 
Etc..

I am trying to join my tables together and return a count where Online = 1 So, the result I am after is:

Name  | TotalNum | Online
Blah  | 3        | 2
Blah1 | 2        | 1
Etc..

So far I have the following query:

SELECT s.Name, s.TotalNum, COUNT(r.Online = 1) AS Online FROM TableX AS r
LEFT JOIN TableY AS s ON s.Store = r.Store
WHERE r.Store = 'xx'

But it just returns a count of the total rows regardless if Online = 1 or 0. Any help appreciated. Thanks

Answer 1

This should do it:

SELECT x.Name, x.TotalNum, SUM(y.Online = 1) Online FROM TableX x
LEFT JOIN TableY y ON x.Store = y.Store
WHERE x.Store = 'xx'

If you also want a 0 if there is no matching element in the TableY then you should do COALESCE the results: COALESCE(SUM(y.Online = 1)).

Additionally, it is no clear what you mean by ID as I don't see any on the query nor in the tables. However, this query works in the bases that you can only one distinct name and TotalNum for a single Store.

Answer 2

It's just a matter of summing online!

SELECT
    s.Name,
    s.TotalNum,
    sum(r.Online) AS Online
FROM TableX AS r
LEFT JOIN TableY AS s ON s.Store = r.Store
WHERE r.Store = 'xx'