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
(This was for my friends who like Mac).
Subscribe to my RSS feed