Using "group by" and "having" with MySQL

With SQL queries you can combine the "GROUP BY" syntax with "HAVING" to return rows that match a certain count etc. This post looks at how to return rows based on a count using having specifically tested on MySQL but it should work for other database servers as well.

An example table might be an orders header table, containing order information, customer id, and the country the order was placed from. A minimal example table structure might look like so:

+-------------------+---------------+------+-----+------------+----------------+
| Field             | Type          | Null | Key | Default    | Extra          |
+-------------------+---------------+------+-----+------------+----------------+
| order_id          | int(11)       | NO   | PRI | NULL       | auto_increment |
| order_date        | date          | NO   | MUL | 0000-00-00 |                |
| status            | tinyint(4)    | NO   | MUL | 0          |                |
| customer_id       | int           | NO   | MUL | 0          |                |
| first_name        | varchar(50)   | NO   |     | NULL       |                |
| last_name         | varchar(50)   | NO   |     | NULL       |                |
| email_address     | varchar(100)  | NO   |     | NULL       |                |
| country_code      | char(2)       | NO   | MUL | NULL       |                |
| order_total       | decimal(10,2) | NO   |     | 0.00       |                |
+-------------------+---------------+------+-----+------------+----------------+

If you wanted to get a count of orders placed by country, you'd run a query like this:

SELECT country_code, COUNT(*)
FROM orders_header
GROUP BY country_code

The first few rows from the result set might look like this:

+--------------+----------+
| country_code | count(*) |
+--------------+----------+
| AE           |       18 |
| AR           |       18 |
| AS           |       45 |
| AT           |       54 |
| AU           |     1277 |
| AZ           |        1 |
...

If you wanted to find out all the countries that had only had one order placed, you could combine the "group by" with "having" like this:

SELECT country_code, COUNT(*)
FROM orders_header
GROUP BY country_code
HAVING COUNT(*) = 1

This would then only return country codes which have a count of 1, like so:

+--------------+----------+
| country_code | COUNT(*) |
+--------------+----------+
| AZ           |        1 |
| BH           |        1 |
| GH           |        1 |
| KG           |        1 |
| KW           |        1 |
| MD           |        1 |
| NP           |        1 |
| QA           |        1 |
| WF           |        1 |
| ZW           |        1 |
+--------------+----------+
10 rows in set (0.18 sec)

As another example, if you wanted to find out all the customers who had placed more than 5 orders you could use the same sort of query like this:

SELECT customer_id, COUNT(*)
FROM orders_header
GROUP BY customer_id
HAVING COUNT(*) > 5

And if you wanted to find all the customers who had spent more than e.g. $1000 in total across all their orders you can use the SUM() function with GROUP BY and HAVING to work it out:

SELECT customer_id, SUM(order_total)
FROM orders_header 
GROUP BY email_address 
HAVING SUM(order_total) > 1000;

There are many possibilities for using GROUP BY with HAVING, as shown in some of the examples above. It can also be used for weeding out duplicate records if you searched for HAVING COUNT(*) > 2 when selecting a specific field.

Related posts:

• "Randomly" order data across multiple pages with MySQL
• Randomly ordering a MySQL result set
• Find the length of the longest string in MySQL