Mysql returns only one row when using Count

Question

Well, I just hit weird behavior that I had never seen before or noticed.

I am using this query:

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

... read 3 elements, but at the same time I want to get full lines.

Problem...

... when I use count, the query returns only one row, but if I delete COUNT(*) AS pages - I get 3 rows, as I assume. Obviously, I missed something.

Answer 1

Yes, a counter is an aggregate statement that returns only one row (without a group by clause)

Maybe make two separate requests? It does not make sense for a row to return data and the total number of rows, because this data does not belong to each other.

If you really want this, 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 that:

 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 deviations of the nested expression depending on your SQL dialect.

Answer 2

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

Note that on most DBMSs, such a request would fail, because it does not make sense.

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