18.3 Aggregate functions: AVG(), SUM(), GROUP BY

Sometimes you may need to execute functions on the data before extracting it. See how this may be done below:

1. Write a SQL statement that shows the average salary of the employees in each location. Display LocationID, Location City, AverageSalary.

   • functions
   • AVG()
   • GROUP BY

2. Write a SQL statement that shows how many employees work in the New York City and Denver locations. Display Location City and Total Employees.

   • COUNT()

3. Write a SQL statement that shows the total salary of the employees for each location. Display Location City and Total.

   • SUM()

4. Create a Summary Query to Display the average and minimum salary of all job positions. Display job position, average salary, and minimum salary.

   • MIN()

5. Calculate the difference between the current salary and the maximum salary of each Regional Manager. Name the calculated field “SalaryDifference.” Show last name, salary, maximum salary, and SalaryDifference.

   • Calculated fields (not the same as functions)