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.

[code]
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)
[/code]

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

[code]
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
[/code]

#2 Using MySQL's SQL_CALC_FOUND_ROWS and performing a second query
[code]
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;
[/code]

[code]
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)
[/code]

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

Database Scheme

[code]
--
-- 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');
[/code]

Related

Referral Note: When you purchase through a referral link (if any) on this page, we may earn a commission.
If you're feeling thankful, you can buy me a coffee or a beer