SQL GROUP BY Statement
SQL GROUP BY Statement
The SQL GROUP BY statement is used to group rows that have the same values in one or more columns. This statement is often used in conjunction with aggregate functions like SUM, COUNT, AVG, MIN, and MAX to summarize data and generate reports.
The syntax for the GROUP BY statement is as follows:
SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;
In this syntax, column1 and column2 are the columns that we want to group by, and aggregate_function is the function that we want to apply to column3.
Here’s an example of using the GROUP BY statement:
SELECT department, SUM(salary)
FROM employees
GROUP BY department;
In this example, we’re grouping the employees by department and calculating the total salary for each department using the SUM function. The result set will contain one row for each department and its total salary.
We can also use the GROUP BY statement with multiple columns. For example:
SELECT department, gender, AVG(salary)
FROM employees
GROUP BY department, gender;
In this example, we’re grouping the employees by department and gender, and calculating the average salary for each group using the AVG function. The result set will contain one row for each unique combination of department and gender, and its average salary.
It’s important to note that any columns in the SELECT statement that are not included in the GROUP BY statement or used in an aggregate function must be in a single-value expression. Otherwise, the query will not execute.
Here’s an example of a query that includes a non-grouped column in an aggregate function expression:
SELECT department, gender, MAX(salary), name
FROM employees
GROUP BY department, gender;
In this example, the name column is not included in the GROUP BY statement or used in an aggregate function, so the query will fail. To fix this, we need to remove the name column from the SELECT statement or include it in a single-value expression.