MySQL: Using COUNT function inside a SELECT statement

Viewed 1461

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

3 Answers
Related