Skip to content

04-aggregate-functions.md

Latest commit

 

History

History
128 lines (93 loc) · 3.53 KB

04-aggregate-functions.md

File metadata and controls

128 lines (93 loc) · 3.53 KB
Raw

Chapter 04: Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. They are essential for summarizing data, finding trends, and performing high-level analysis.

4.1 COUNT — How Many?

The COUNT() function returns the number of rows that match a specified criterion.

-- Total number of orders
SELECT COUNT(*) AS total_orders FROM orders;

-- COUNT(*) counts all rows, including NULLs.
-- COUNT(column) counts only non-NULL values!
SELECT
    COUNT(*)                            AS total_orders,
    COUNT(order_delivered_customer_date) AS delivered_orders
FROM orders;

The difference between these two counts reveals how many orders have not yet been delivered.

4.2 SUM — What's the Total?

SUM() adds up all the values in a numeric column.

-- Total revenue from all order items
SELECT SUM(price) AS total_revenue FROM order_items;

-- Total revenue + freight combined
SELECT SUM(price + freight_value) AS grand_total FROM order_items;

4.3 AVG — What's the Average?

AVG() calculates the mean of a numeric column.

-- Average item price
SELECT AVG(price) AS avg_price FROM order_items;

-- Average review score
SELECT AVG(review_score) AS avg_review_score FROM order_reviews;

4.4 MIN and MAX — Finding Extremes

MIN() and MAX() find the smallest and largest values in a column, respectively. They work with numbers, strings, and dates.

-- Cheapest and most expensive items
SELECT
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM order_items;

-- Date range of our data
SELECT
    MIN(order_purchase_timestamp) AS first_order,
    MAX(order_purchase_timestamp) AS last_order
FROM orders;

4.5 Combining Multiple Aggregates

You can use multiple aggregate functions in a single SELECT statement to get a comprehensive overview.

SELECT
    COUNT(*)           AS total_items,
    SUM(price)         AS total_revenue,
    AVG(price)         AS avg_price,
    MIN(price)         AS min_price,
    MAX(price)         AS max_price
FROM order_items;

4.6 Aggregates with WHERE

Aggregates are often more useful when combined with filters to analyze specific subsets of data.

-- Average price of items above 100 BRL
SELECT AVG(price) AS avg_price_above_100
FROM order_items
WHERE price > 100;

-- Total revenue from credit card payments only
SELECT SUM(payment_value) AS credit_card_revenue
FROM order_payments
WHERE payment_type = 'credit_card';

Exercises

  1. What is the total freight cost for all orders?
  2. What are the average, minimum, and maximum payment installments?
  3. How many unique customers placed orders?
  4. What is the average review score for orders that were canceled?
  5. What is the total revenue from items sold by sellers in state 'SP'?
  6. What is the average product description length for products with at least 3 photos?
Solutions 
-- Exercise 1
SELECT SUM(freight_value) FROM order_items;

-- Exercise 2
SELECT AVG(payment_installments), MIN(payment_installments), MAX(payment_installments) FROM order_payments;

-- Exercise 3
SELECT COUNT(DISTINCT customer_id) FROM orders;

-- Exercise 4
SELECT AVG(r.review_score) FROM order_reviews r JOIN orders o ON r.order_id = o.order_id WHERE o.order_status = 'canceled';

-- Exercise 5
SELECT SUM(oi.price) FROM order_items oi JOIN sellers s ON oi.seller_id = s.seller_id WHERE s.seller_state = 'SP';

-- Exercise 6
SELECT AVG(product_description_lenght) FROM products WHERE product_photos_qty >= 3;