Search code examples
mysqlcountgroup-concat

MySQL COUNT With group_concat

I have to query multiple tables in a database to create a list of items, then in order to display the list in pages I have to get the total number of rows using COUNT.

My SQL is as follows:

SELECT
    j.jid,
    j.reference,
    group_concat(COALESCE(p.canview, u.canview)) AS canview
FROM jobs j
LEFT JOIN usergroups u ON (u.gid IN (1,2,3))
LEFT JOIN permissions p ON (p.jid = j.jid) AND (p.gid = u.gid)
GROUP BY j.jid
HAVING FIND_IN_SET('1', canview) > 0
LIMIT 0, 5

Essentially all I want to know is if it's possible, by altering the above query, that I can get the total number of rows returned before the LIMIT. I've tried added a COUNT(j,jid) but that returns 3 (I think this is the number of rows concatenated with the group_concat) instead of the desired 9 rows currently in the jobs database that have canview set to 1.

Thanks in advance for any help.

Solution

  • Run the query.

    Then run a second query to count the rows:

    select found_rows()

    It is documented here.

    EDIT:

    To get the total without rows, use the SQL_CALC_FOUND_ROWS keyword it the select:

    select SQL_CALC_FOUND_ROWS . . .

    Then found_rows() will return the total without the limit.