SQL英文版教学课件：4-GROUP BY clause

1、10/8/20221SQL(4)GROUP BY clause 10/8/20222GROUP BY clause 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:10/8/20223GROUP BY c

2、lause syntax:SELECT column1, SUM(column2)FROM list-of-tablesGROUP BY column-list; 10/8/20224Example:Lets say you would like to retrieve a list of the highest paid salaries in each dept: SELECT max(salary), deptFROM employee GROUP BY dept; Example:This statement will select the maximum salary for the

3、 person 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. 10/8/2022510/8/20226GROUP BY - Multiple Grouping Columns - What if?What if you ALSO want to display their lastname for t

4、he query below: SELECT max(salary), deptFROM employee GROUP BY dept; GROUP BY - Multiple Grouping Columns - What if?What youll need to do is: SELECT lastname, max(salary), deptFROM employee GROUP BY dept, lastname; This is a called multiple grouping columns. 10/8/2022710/8/20228Example:take a look a

5、t the items_ordered table.Lets say you want to group everything of quantity 1 together, everything of quantity 2 together, everything of quantity 3 together, etc.If you would like to determine what the largest cost item is for each grouped quantity (all quantity 1s, all quantity 2s, all quantity 3s,

6、 etc.), you would enter:10/8/20229Example:SELECT quantity, max(price)FROM items_orderedGROUP BY quantity; 10/8/202210Review Exercises How many people are there in each unique state in the customers table? Select the state and display the number of people in each. Hint: count is used to count rows in

7、 a column, sum works on numeric data only. 10/8/202211Review Exercises From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups. 10/8/202212Review ExercisesHow many orders did e

8、ach customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders. GROUP BY -Exercise Answers Exercise #1 SELECT state, count(state)FROM customersGROUP BY state;10/8/202213GROUP BY -Exercise Answers 10/8/202214Exercise #2 SELECT item, max(price), min(price)FROM items_orderedGROUP BY item ; 10/8/20