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

Mosty Mostacho · Accepted Answer · 2013-12-11 04:35:14Z

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.

answered Dec 11, 2013 at 4:35

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Riples Over a year ago

Damn, that simple hey! I did try the SUM function, but had my query muddled up. Thanks heaps for the speedy reply. Also, for the record, ID was meant to be STORE sorry for the confusion!

Bohemian · Accepted Answer · 2013-12-11 04:50:29Z

1

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'

answered Dec 11, 2013 at 4:50

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Comments

Alf · Accepted Answer · 2013-12-11 05:00:54Z

1

This method using count also worked for me:

SELECT tablex.name, tablex.storeID, tablex.totalNum, count(online) FROM tablex
INNER JOIN tabley
ON tablex.storeID = tabley.storeID
WHERE online = 1
GROUP BY name

My result

name    storeID     totalNum    count(online)
blaA    1           3           2
blaB    2           2           1

answered Dec 11, 2013 at 5:00

Alf

8621 gold badge8 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL: Using COUNT function inside a SELECT statement

Table X

Table Y

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Table X

Table Y

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related