Section 6. GROUP BY Statements

https://www.udemy.com/the-complete-sql-bootcamp

 

MIN MAX SUM and AVG

example

 

GROUP BY

The GROUP BY  clause divides the rows returned from the SELECT statement into groups.
For each group, you can apply an aggregate function, for example:
– calculating the sum of items
– count the number of items in the groups

GROUP BY Syntax

Group By Syntax by itself works like DISTINCT operator without aggregate function.
Some SQL engines will complain and require you to put an agrregate function somewhere at SELECT statement.

GROUP BY can be used with the ORDER BY operator

GROUP BY can be used with the COUNT operator

GROUP BY can be used with the AVG operator

 

CHALLENGE: GROUP BY

1. We have two staff members with Staff IDs 1 and 2. We want to give a bonus to the staff member that handled the most payments.
How many payments did each staff member handle? And how much was the total amount processed by each staff member.
Solution

2. Corporate headquarters is auditing our store. They want to know the average replacement cost of movies by rating. For example, R rated movies have an average replacement cost of $20.23.
Solution

3. We want to send coupons to the 5 customers who have spent the most amount of money. Get me the customer ids of the top 5 spenders.
Solution

 

HAVING

We often use the HAVING clause in conjunction with the GROUP BY clause to filter group rows that do not satisfy a specified condition.
Having Syntax

The HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies.
This is the main difference between the HAVING and WHERE clauses.
Example

Use WHERE and HAVING Together

Challenge : HAVING
1. We want to know what customers are eligible for our platinum cards. The requirements are that the customer has at least a total of 40 transaction payments. What customers(by customer_id) are eligible for the credit card?
Solution

2. When grouped by rating, what movie ratings have an average rental duration of more than 5 days?

 

ASSESSMENT TEST 1

1. Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2.
Answer

 

2. How many films begin with the letter J?
The answer should be 20.

3. What customer has the highest customer ID number whose name starts with an ‘E’ and has an address ID lower than 500?
Answer

 

댓글 남기기

Close Menu