Mysql returns only one row when using Count

Question

Well I've just hit a weird behaviour that I've never seen before, or haven't noticed.

I'm using this query:

  SELECT *, 
         COUNT(*) AS pages 
    FROM notis 
   WHERE cid = 20 
ORDER BY nid DESC 
   LIMIT 0, 3

...to read 3 items but while doing that I want to get the total rows.

Problem is...

...when I use count the query only returns one row, but if I remove COUNT(*) AS pages -- I get the 3 rows as I'm suppose to. Obviously, i'm missing something here.

Answer 1

Using an aggregate function without a GROUP BY will always return one row, no matter what. You must use a GROUP BY if you want to return more than one row.

Note that on most RDBMS, such a query would have failed because it makes no sense.

Answer 2

Yeah, the count is an aggregate operator, which makes only one row returned (without a group by clause)

Maybe make two separate queries? It doesn't make sense to have the row return the data and the total number of rows, because that data doesn't belong together.

If you really really want it, you can do something like this:

SELECT *, (select count(*) FROM notis WHERE cid=20) AS count FROM notis WHERE cid=20 ORDER BY nid DESC LIMIT 0,3

or this:

SELECT N.*, C.total from notis N join (select count(*) total FROM notis WHERE cid=20) C WHERE cid=20) AS count FROM notis WHERE cid=20 ORDER BY nid DESC LIMIT 0,3

With variances on the nested expression depending on your SQL dialect.

Answer 3

This is inefficient, but will work:

SELECT 
   *,
   (SELECT COUNT(*) FROM notis WHERE cid=20) AS pages
FROM notis 
WHERE cid=20 
ORDER BY nid DESC 
LIMIT 0,3