Wednesday, January 9, 2013

GROUP BY Clause


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;