How do you return selected columns along with COUNT(*) in one query?-CodePudding

Edit: Thank you Tim Biegeleisen for pointing out a typo in my question. I had 50 in both limit and offset in my actual code, so I haven't noticed until you pointed it out.

I am trying to limit the rows that are returned from my query, but still keep the total count of rows in the table.

I have attempted the following:

SELECT 
    first,
    last_name,
    date_joined,
    age,
    COUNT(*) AS num_people
FROM
    foo_table
WHERE
    city = 'bar'
ORDER BY date_joined DESC
LIMIT 0, 50;

However, this only returns a single row, a seemingly arbitrary one at that. Changing it to

5 AS num_people

Correctly returns everything and simply implements a 5 in a seperate column num_people. Why does this break when I attempt to use COUNT(*)? Is there a better alternative to return both the total count as well as the selected rows?

CodePudding user response：

If you're using MySQL 8 , and your requirement is to return a total row count along with the result set, you may consider using COUNT() as an analytic function:

SELECT first, last_name, date_joined, age, COUNT(*) OVER () AS num_people
FROM foo_table
WHERE city = 'bar'
ORDER BY date_joined DESC
LIMIT 50;