Mysql - How to get the total number of rows of a complex query with joins, group

Many times we need just the total number of rows that are produced by a single SELECT statement.
If you have even just a little experience with MySQL or other database(s) you'll think of COUNT(*) or similar function.

The query below uses COUNT(*) function but does not return the total number of rows.
It actually returns how many orders each customer has made.
When a query contains GROUP BY clause the situation changes.

mysql> SELECT c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id  GROUP BY c.id;
+----+-----------+------------------------+-----+
| id | cust_name | cust_email             | cnt |
+----+-----------+------------------------+-----+
|  1 | Slavi     | slavi at slavi dot biz |   2 |
|  2 | Test      | test at test dot com   |   1 |
+----+-----------+------------------------+-----+
2 rows in set (0.00 sec)

What if we want to create a pagination ?
Then we'll need to calculate the total number of rows for this query.

Solutions:
#1 Putting the main query into a subquery

SELECT count(*) as total_rows FROM (SELECT c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id  GROUP BY c.id) as tab

#2 Using MySQL's SQL_CALC_FOUND_ROWS and performing a second query

SELECT SQL_CALC_FOUND_ROWS c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id GROUP BY c.id LIMIT 10;

mysql> SELECT SQL_CALC_FOUND_ROWS c.*, COUNT(c.id) as cnt FROM `test_orders` AS o INNER JOIN test_customers AS c ON o.customer_id = c.id  GROUP BY c.id;
+----+-----------+------------------------+-----+
| id | cust_name | cust_email             | cnt |
+----+-----------+------------------------+-----+
|  1 | Slavi     | slavi at slavi dot biz |   2 |
|  2 | Test      | test at test dot com   |   1 |
+----+-----------+------------------------+-----+

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

Note:
For "mysql>" is means that the commands are executing in a MySQL console.

Database Scheme

--
-- Table structure for table `test_customers`
--

CREATE TABLE `test_customers` (
`id` int(11) NOT NULL auto_increment,
`cust_name` varchar(255) NOT NULL,
`cust_email` varchar(255) NOT NULL,
KEY `id` (`id`,`cust_name`,`cust_email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `test_customers`
--

INSERT INTO `test_customers` (`id`, `cust_name`, `cust_email`) VALUES
(1, 'Slavi', 'slavi at slavi dot biz'),
(2, 'Test', 'test at test dot com');

-- --------------------------------------------------------

--
-- Table structure for table `test_orders`
--

CREATE TABLE `test_orders` (
`order_id` int(11) NOT NULL auto_increment,
`customer_id` int(11) NOT NULL,
`order_date` datetime NOT NULL,
PRIMARY KEY  (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `test_orders`
--

INSERT INTO `test_orders` (`order_id`, `customer_id`, `order_date`) VALUES
(1, 1, '2009-01-06 20:00:00'),
(2, 1, '2009-01-06 20:30:00'),
(3, 2, '2009-01-06 19:00:00');

Related

• http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html
• http://dev.mysql.com/doc/refman/5.0/en/information-functions.html
• http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/