Skip to content
Search

SQL:GroupBy

lastmod 2024-12-04created 2024-12-044 edits

Aggregate Function

COUNT (count) 

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

SUM (sum)

AVG (average)

MIN (minimum)

MAX (maximum)

Using without aggregate functions

If GROUP BY is used without aggregate functions, it simply returns the data grouped by each unique combination. For example: 

SELECT department, job_title
FROM employees
GROUP BY department, job_title;

In this case, only the unique combinations of department and job_title are returned. This is useful when you want to remove duplicates without summarizing the data.

Rules

Every column listed in the SELECT clause must either be included in GROUP BY or processed by an aggregate function. Otherwise, an SQL syntax error occurs.

For example, the following raises an error: 

SELECT department, job_title, salary
FROM employees
GROUP BY department, job_title;

Reason for the error: salary is neither in GROUP BY nor processed by an aggregate function.

Correct example: 

SELECT department, job_title, MAX(salary) AS max_salary
FROM employees
GROUP BY department, job_title;

Conclusion:

  • GROUP BY can be used without aggregate functions, but it is usually used to summarize or aggregate data.
  • Columns appearing in the SELECT clause must be included in GROUP BY or processed by an aggregate function.

See also

Favorite site

author Zer0 sitemap