SELECT - Having results

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SELECT [flags] {*|column|expression}[, ...]
FROM table[, ...]
[WHERE condition]
[GROUP BY condition]
[HAVING condition]
[other clauses] [options]

Explanation

The HAVING clause is similar to the WHERE clause, but it is used for conditions returned by aggregate functions (e.g., AVG(), MIN( ), and MAX( )). For older versions of MySQL, you must use aliases for aggregate functions in the main clause of the SELECT statement.

Examples

Here is an example of how you can use this clause:

SELECT CONCAT(name_first, ' ', name_last) AS 'Name', total_order
FROM orders
JOIN employees ON sales_rep = emp_id
JOIN branches USING (branch_id)
WHERE location = 'New Orleans'
GROUP BY sales_rep
HAVING MAX(total_order);

This SQL statement retrieves from the employees table a list of employee names for all employees located in the New Orleans branch office. From this list, the statement refines the results by grouping the data for each representative together and determines the sum of each one's total_order column. Because of the MAX() function, it displays data only for the row with the maximum number. The JOIN clause is described in its own documentation page.