MySQL "group by" and "having"

With SQL queries you can combine the "GROUP BY" syntax with "HAVING" to return rows that match a certain count etc. This blog entry looks at how to return rows based on a count.

An example of counting the orders placed by country

SELECT countryCode, COUNT(*) FROM orders GROUP BY countryCode

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

+--------------+----------+
| countryCode | count(*) |
+--------------+----------+
| US           |     1134 |
| UK           |      418 |
| PL           |      405 |

An example showing all the countries "having" only one order placed

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

This would then only return country codes which have a count of one.

+--------------+----------+
| countryCode  | COUNT(*) |
+--------------+----------+
| BR           |        1 |
| BG           |        1 |
| GE           |        1 |
| GE           |        1 |
| AT           |        1 |
| AI           |        1 |

An example showing customers "having" more than 5 orders.

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

An example showing customers "having" spent more arbitary amount in total across all their orders.

SELECT customerId, SUM(orderTotal) FROM orders GROUP BY emailAddress HAVING SUM(orderTotal) > 1000;

There are many possibilities for using GROUP BY with HAVING.

select, group by.

2017-12-07
YouTube Twitter GitHub