Get result row count for individual SELECT statements with UNION ALL in MySQL when using LIMIT

Question

Hi all. I need to get the row count for individual SELECT statements in an UNION ALL SELECT query that uses LIMIT. The MySQL docs are pretty clear about getting the global row count for the query (place individual SELECTs in parenthesis and place an SQL_CALC_FOUND_ROWS only in the first statement, then get FOUND_ROWS() the usual way). However, I also need the result row count for the individual SELECT statements. The query, simplified:

(SELECT SQL_CALC_FOUND_ROWS `field1`,`field2` FROM `db`.`table1` WHERE `id`>1000)
UNION ALL
(SELECT `field1`,`field2` FROM `db`.`table2` WHERE `id`>1000)
UNION ALL
...
(SELECT `field1`,`field2` FROM `db`.`tableN` WHERE `id`>1000)
LIMIT 0,10

If SQL_CALC_FOUND_ROWS is placed in every SELECT statement an "Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'" error is issued. Google this a lot, read related messages here, to no avail. Might be something really simple, I just can't get my mind arround it. Many thanks.

Answer 1

You're getting that error because SQL_CALC_FOUND_ROWS can be used on a query to return a single number into FOUND_ROWS().

You can get back counts doing this:

SELECT 'table1' AS tablename, COUNT(1) AS rowcount FROM table1 WHERE id > 1
UNION ALL
SELECT 'table2' AS tablename, COUNT(1) AS rowcount FROM table2 WHERE id > 1
...
SELECT 'tableN' AS tablename, COUNT(1) AS rowcount FROM tableN WHERE id > 1

if that helps.

If you want to return the rows from the tables with a count then modify it:

SELECT field1, field2, (SELECT COUNT(1) FROM table1 WHERE id > 1000) FROM table1 WHERE id > 1000
UNION ALL
SELECT field1, field2, (SELECT COUNT(1) FROM table2 WHERE id > 1000) FROM table2 WHERE id > 1000
...
SELECT field1, field2, (SELECT COUNT(1) FROM tableN WHERE id > 1000) FROM tableN WHERE id > 1000

From your question, it's not precisely clearly what you're trying to achieve.