SQL: GROUP BY And HAVING Clause

Aggregate functions often need an added GROUP BY statement.

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

The SQL GROUP BY clause can be used in an SQL SELECT statement to collect data across multiple records and group the results by one or more columns.

        The syntax for the SQL GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n;

aggregate_function can be a function such as SQL SUM function, SQL COUNT function, SQL MIN function, or SQL MAX function.

GROUP BY Clause - Using the SUM function example

You could also use the SQL SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

GROUP BY Clause - Using the COUNT function example

You could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

GROUP BY Clause - Using the MIN function example

You could also use the SQL MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

GROUP BY Clause - Using the MAX function example

You could also use the SQL MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;

--------------------------------

SQL: HAVING Clause

The SQL HAVING clause is used in combination with the SQL GROUP BY clause. It can be used in an SQL SELECT statement to filter the records that a SQL GROUP BY returns.

       The syntax for the SQL HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

aggregate_function can be a function such as SQL SUM function, SQL COUNT function, SQL MIN function, or SQL MAX function.

SQL HAVING Clause - Using the SUM function example

You could also use the SQL SUM function to return the name of the department and the total sales (in the associated department). The SQL HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

SQL HAVING Clause - Using the COUNT function example

You could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The SQL HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

SQL HAVING Clause - Using the MIN function example

You could also use the SQL MIN function to return the name of each department and the minimum salary in the department. The SQL HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

SQL HAVING Clause - Using the MAX function

For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department. The SQL HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;