AI Hints
Homeโ€บSQL Tutorialโ€บCounting and Totals
SQL ยท Lesson 34 of 126

Practice: The Staff Numbers

Five aggregates together.

โฑ 2 min readโ–ถ Video at 47:54๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen is planning next year's budget. She wants three numbers about her staff. How many people work here, how much she pays in total, and the average salary, rounded to whole dollars.

Practice: The Staff Numbers
From the video: Practice: The Staff Numbers
  • Next year's budget
  • Staff, total pay, average pay
  • Rounded to whole dollars

Staff budget

Pause the video, and try it yourself. You'll need count, sum, and average, with round, all on the staff table. Here's one solution. Count star, sum of salary, and the average salary rounded to zero decimal places.

โœ๏ธ Exercise: Staff budget

One row: number of staff, total salary, average salary (rounded)

Show hint

COUNT(*), SUM(salary), ROUND(AVG(salary), 0)

Show solution
solution.sqlsql
SELECT COUNT(*)              AS staff,
       SUM(salary)           AS total_pay,
       ROUND(AVG(salary), 0) AS average_pay
FROM staff;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ staff โ”‚ total_pay โ”‚ average_pay โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 8     โ”‚ 337500    โ”‚ 42188.0     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Quick check

Quick check. The customers table has five hundred rows, and thirty one customers have no email. What does count of email return? Pause and choose. The answer is C, four hundred and sixty nine. Count of a column skips the nulls.

Quick check
COUNT(email) on 500 customers, 31 with no email

One more

One more. You want the number of different genres in the books table. Which query is right? Pause and choose. The answer is D. Count distinct genre counts each genre only once. Count of genre would count all thirty books.

Quick check
How many different genres are there?

Summary

You can now turn a whole table into a handful of useful numbers. But Helen's next question is harder. Not just how many in total, but how many for each genre. That needs one of the most important ideas in SQL. Group by.

Key takeaways
  • Five aggregates
  • WHERE filters first
  • Next: for each group
Next up: GROUP BY โ€” one answer per group