SQL: GROUP BY CLAUSE

SQL GROUP BY clause with examples.

DESCRIPTION

SQL GROUP BY clause  used in a SELECT statement to collect data having multiple records and group them into results by one or more columns.

SYNTAX

·         SELECT expression1, expression2, ... expression_n,   aggregate_function (expression) FROM tables WHERE conditions GROUP BY expression1, expression2, ... expression_n;

·         expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

·         aggregate_function  function as SUM function, COUNT function, MIN function, or MAX function.

·         tables are the tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

·         conditions are conditions that must be met for the records to be selected.

EXAMPLE - USING SUM FUNCTION

SQL GROUP BY query example that uses the SQL SUM function.

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

This GROUP BY example uses the SUM function to return the name of the department and the total sales (for the department).

EXAMPLE - USING COUNT FUNCTION

GROUP BY clause with the SQL COUNT function.

This GROUP BY example with COUNT function returns the name of the department and the number of employees (in that department where salary is 20000;

SELECT department, COUNT(*) AS "No.  employee" FROM employees WHERE salary > 20000 GROUP BY department;

EXAMPLE - USING MIN FUNCTION

GROUP BY clause with the SQL MIN function.

GROUP BY example uses with MIN function to return the name of each department and minimum salary in the department.

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