$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Filtering groups with HAVING #

WHERE filters individual rows before they are grouped, so it can't refer to an aggregate function like COUNT(*). To filter groups after they've been aggregated, for example, "only show categories with more than 5 products", you need HAVING.

SELECT column_name, AGGREGATE_FUNCTION(other_column) AS agg
FROM table_name
GROUP BY column_name
HAVING AGGREGATE_FUNCTION(other_column) > value;

HAVING works just like WHERE, except its condition is checked against the aggregated value of each group instead of each raw row, and it always comes after GROUP BY.

Example #

SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
HAVING COUNT(*) > 5;

Result:

+-----------+-------+
|  status   | total |
+-----------+-------+
| Cancelled |   8   |
|  Shipped  |   7   |
+-----------+-------+

Here, orders is grouped by status, and only the groups with more than 5 orders are kept, Delivered and Pending had 5 or fewer orders so they are dropped entirely.

Exercise #

Write a command that groups the orders table by customer_id, counting how many orders each customer has, and keeps only the customers with more than 2 orders.

  • The result has a customer_id column and a total column.
  • Only customers with more than 2 orders appear, each with the correct order count.
Solution 
SELECT customer_id, COUNT(*) AS total
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2;
Output will be displayed here