🎓 A1Learner
🔍

SQL Sum and Avg

SUM and AVG are aggregate functions that work on a column of numbers. SUM adds the values in the column, and AVG gives their average.

Add a column

SUM adds every stock value in products. The eight values total 1127 items.

SELECT SUM(stock) AS total_stock
FROM products;

Average a column

AVG divides the price total by the number of prices. The result keeps every decimal place the engine returns.

SELECT AVG(price) AS average_price
FROM products;

Round the average

ROUND cuts a number to the places named. Here ROUND wraps AVG and keeps two decimal places.

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Round a total

A money total reads best at two decimal places. ROUND cuts the SUM result to two places here.

SELECT ROUND(SUM(price), 2) AS total_price
FROM products;

Example

SELECT SUM(quantity) AS items_ordered, ROUND(AVG(quantity), 1) AS average_items
FROM orders;

Key points

  • SUM: an aggregate function that adds the values in a column
  • AVG: an aggregate function that gives the average of a column
  • Both functions skip rows where the column holds NULL.
  • ROUND: a function that cuts a number to the decimal places named after the comma
  • ROUND(price, 2) keeps two decimal places, the shape money uses.
  • An AS alias gives each total a clear column name.

In this example, SUM adds the quantity from all ten orders and returns 26 items. AVG averages the same column. ROUND keeps one decimal place on that average. The two results sit side by side in one row.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does ROUND(AVG(price), 2) return?