Welcome back! 👋
So far, we’ve learned how to:

• Retrieve data with SELECT
• Filter data with WHERE
• Use advanced filters like IN, BETWEEN, LIKE, and handle NULL

In this lesson, we’ll take it further by learning how to organize and summarize data using:

1. ORDER BY – sort your results
2. GROUP BY – group similar data together
3. Aggregate functions – summarize data like counts, sums, and averages
4. HAVING – filter aggregated results

By the end of this lesson, you’ll be able to analyze real-world data like a pro!

Sorting Data with ORDER BY

Sometimes, you want your results in a specific order. That’s where ORDER BY comes in.

Example 1: Sort customers by signup date (oldest to newest)

SELECT full_name, signup_date FROM customers ORDER BY signup_date ASC; Copy

• ASC → ascending (default)
• DESC → descending

Example 2: Sort customers by signup date (newest first)

SELECT full_name, signup_date FROM customers ORDER BY signup_date DESC; Copy

Example 3: Sort by multiple columns

SELECT full_name, country, city FROM customers ORDER BY country ASC, city DESC; Copy

• First sorts by country (ascending)
• Then, within each country, sorts by city (descending)

Aggregating Data with Functions

Aggregate functions summarize data.

Common functions:

Function	What it Does	Example
COUNT()	Counts rows	Number of customers
SUM()	Adds up numbers	Total sales
AVG()	Average value	Average product price
MIN()	Minimum value	Cheapest product
MAX()	Maximum value	Most expensive product

Example 1: Count total customers

SELECT COUNT(*) AS total_customers FROM customers; Copy

Example 2: Sum of all orders

SELECT SUM(total_amount) AS total_sales FROM orders; Copy

Example 3: Average product price

SELECT AVG(price) AS avg_price FROM products; Copy

Example 4: Highest and lowest product price

SELECT MAX(price) AS highest_price, MIN(price) AS lowest_price FROM products; Copy

Grouping Data with GROUP BY

GROUP BY allows you to aggregate data by category.

Example 1: Number of orders per customer

SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id; Copy

• Groups orders by each customer
• Counts how many orders each customer placed

Example 2: Total sales per customer

SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id; Copy

Filtering Aggregated Data with HAVING

WHERE filters before aggregation, while HAVING filters after aggregation.

Example 1: Customers with total orders more than 1

SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 1; Copy

Example 2: Customers who spent more than $1000

SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 1000; Copy

Real-World Example – Orders and Products

Suppose you want to know how many times each product was ordered:

SELECT product_id, SUM(quantity) AS total_quantity_sold FROM order_items GROUP BY product_id ORDER BY total_quantity_sold DESC; Copy

• Shows which products are most popular
• Groups by product_id and sums quantity
• Orders results descending

Practice Queries

Try these exercises with your sample database:

1. Count how many customers are from Pakistan.
2. Find the total amount spent by each customer, sorted from highest to lowest.
3. Count how many products are in each category.
4. Find products with total sales greater than $1000.

Writing and running queries yourself is the fastest way to become confident in SQL.

Lesson Summary

• ORDER BY sorts your results in ascending or descending order
• Aggregate functions summarize data: COUNT, SUM, AVG, MIN, MAX
• GROUP BY groups data by one or more columns
• HAVING filters results after aggregation
• Combining GROUP BY and aggregates lets you answer real business questions

In Lesson 6, we’ll dive into SQL JOINs, one of the most important topics for combining multiple tables:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• Real-world examples with customers, orders, and order_items