8. GROUP BY Clause

The GROUP BY clause in SQL is used to arrange identical data into groups. This is particularly useful for applying aggregate functions like SUM, MAX, MIN, COUNT, and AVG on grouped data. Here is a conceptual overview and example usage of the GROUP BY clause.

8.1. Syntax

SELECT column1, 
       aggregate_function(column2)
FROM table_name
GROUP BY column1;

In this syntax:

• column1: The column to group by.
• aggregate_function: The function to apply to each group (e.g., SUM, MAX, MIN, COUNT).
• table_name: The table from which to select data.

8.2. Example: Highest Paid Salaries in Each Department

To retrieve a list of the highest paid salaries in each department:

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

This query selects the maximum salary for each unique department. The result will display the highest salary and the corresponding department.

8.3. Multiple Grouping Columns

If you need to include additional columns in the grouping, such as displaying the last name along with the department and highest salary:

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

This query will group the results by both department and last name, showing the highest salary for each combination.

8.4. Exercises

8.4.1. Exercise 1: Count of People in Each State

How many people are in each unique state in the customers table? Select the state and display the number of people in each state.

SELECT state, COUNT(state)
FROM customers
GROUP BY state;

8.4.2. Exercise 2: Maximum and Minimum Price for Each Item

From the items_ordered table, select the item, maximum price, and minimum price for each specific item.

SELECT item, MAX(price), MIN(price)
FROM items_ordered
GROUP BY item;

8.4.3. Exercise 3: Number of Orders per Customer

How many orders did each customer make? Use the items_ordered table. Select the customer ID, number of orders they made, and the sum of their orders.

SELECT customerid, COUNT(customerid), SUM(price)
FROM items_ordered
GROUP BY customerid;

8.5. Verification

After running the above queries, verify that the results match the expected outputs. For instance, ensure that the maximum price in each quantity group is indeed the highest price for that group, and the counts and sums are correct for each group in the exercises.