Sabtu, 27 September 2014
Description
The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.Syntax
The syntax for the SQL GROUP BY clause is:SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;
Parameters or Arguments
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 can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
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
Let's look at a SQL GROUP BY query example that uses the SQL SUM function.This GROUP BY example uses the SUM function to return the name of the department and the total sales (for the department).
SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department;Because you have listed one column (the department field) in your SQL SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY Clause. The department field must, therefore, be listed in the GROUP BY clause.
Example - Using COUNT function
Let's look at how we could use the GROUP BY clause with the SQL COUNT function.This GROUP BY example uses the COUNT function to return the department and the number of employees (in the department) that make over $25,000 / year.
SELECT department, COUNT(*) AS "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department;
Example - Using MIN function
Let's next look at how we could use the GROUP BY clause with the SQL MIN function.This GROUP BY example uses the 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;
Example - Using MAX function
Finally, let's look at how we could use the GROUP BY clause with the SQL MAX function.This GROUP BY example uses the 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;
Source : techonthenet.com
