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

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.