Mysql count not working?

Question

I have a query

SELECT 
count(*) as count 
from matches 
WHERE team1 = 9 
ORDER BY data DESC, id ASC LIMIT 10

The max result I should expect isn't supposed to be 10? I am getting 15 as result, am I doing something wrong?

try SELECT count(*) from matches WHERE team1 = 9 ORDER BY data DESC, id ASC LIMIT 10 — plain jane
– plain jane, Commented Nov 13, 2013 at 5:15
Is it possible to have an alias count? if it's a reserve word in mysql — Drixson Oseña
– Drixson Oseña, Commented Nov 13, 2013 at 5:16
It is fine to use count as a field alias. It's the name of a function, not actually a reserved word ("SELECT" is a reserved word). — staticsan
– staticsan, Commented Nov 13, 2013 at 5:18

Andreas Wederbrand · Accepted Answer · 2013-11-13 05:25:35Z

3

You need to do your limit in an inner select.

select count(*) from
(select * from matches WHERE team1 = 9 ORDER BY data DESC, id ASC LIMIT 10) ten_rows

sql fiddle

answered Nov 13, 2013 at 5:25

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

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

Comments

staticsan · Accepted Answer · 2013-11-13 05:16:53Z

1

The LIMIT 10 clause applies after the SELECT ... part of the query. So it's counting the rows and putting that number into a single row before applying LIMIT 10.

If you were using the API and asking it how many rows there were in a resultset, then you would indeed get the reply of 10 from the query SELECT * FROM ... LIMIT 10.

answered Nov 13, 2013 at 5:16

staticsan

30.6k5 gold badges64 silver badges72 bronze badges

Comments

Krish R · Accepted Answer · 2013-11-13 05:22:57Z

1

You are just get the count values, limit is not supposed to work for you count rows.

Using LIMIT you will not limit the count or sum but only the returned rows.

In other words, It counts no.of records from table and returned as single row. Your select query matched rows count is 15.

edited Nov 13, 2013 at 5:22

answered Nov 13, 2013 at 5:17

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

Comments

Zeeshan · Accepted Answer · 2013-11-13 05:28:41Z

0

Try to use this,

SELECT 
COUNT(*) AS COUNT 
FROM matches 
WHERE team1 = 9 
ORDER BY DATA DESC, id ASC

answered Nov 13, 2013 at 5:28

Zeeshan

1,68314 silver badges18 bronze badges

Collectives™ on Stack Overflow

Mysql count not working?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related