3. GROUP BY clause in SQL

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. 

This can best be explained by an example:

GROUP BY clause syntax:

 
SELECT column1, 

SUM(column2)
 
FROM "list-of-tables"
 
GROUP BY "column-list";

Let's say you would like to retrieve a list of the highest paid salaries in each dept:

 SELECT max(salary), dept
 
FROM employee 
 
GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

Multiple Grouping Columns - What if I wanted to display their lastname too?

SELECT max(salary), dept

FROM employee

GROUP BY dept;


What you'll need to do is:


SELECT lastname, max(salary), dept
FROM employee
GROUP BY dept, lastname;


This is a called "multiple grouping columns".