Lesson 7 - Aggregate Functions

Lesson Overview

Use functions like SUM, AVG, COUNT, MAX, and MIN to analyse data from a database of sales of mobile phones.

Understanding Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single result. Common aggregate functions include:

SUM(): Calculates the total of a numeric column
AVG(): Calculates the average value
COUNT(): Counts the number of rows
MAX(): Finds the maximum value
MIN(): Finds the minimum value

Task: Analyze Phone Sales Data

Let's analyze mobile phone sales using aggregate functions:

Calculate total sales revenue:

SELECT SUM(price * quantity) AS total_revenue FROM sales

Find average price of phones:

SELECT AVG(price) AS average_price FROM sales

Count total number of sales:

SELECT COUNT(*) AS total_sales FROM sales

Find the most expensive phone:

SELECT model, MAX(price) AS max_price FROM sales

Find the cheapest phone:

SELECT model, MIN(price) AS min_price FROM sales

Using GROUP BY with Aggregate Functions

You can combine aggregate functions with GROUP BY to calculate values for different groups of data:

SELECT model, SUM(quantity) AS total_sold
FROM sales
GROUP BY model;

This query calculates the total quantity sold for each phone model.

Lesson Summary

In this lesson, we've learned how to use aggregate functions to analyze and summarize data. These functions are powerful tools for extracting meaningful insights from large datasets.

Onwards To Lesson 8