Python MySQL - Aggregation (E-Commerce Platform)

What You'll Learn Here

COUNT — counting products, orders, customers
SUM — calculating total revenue
AVG — average order value and product prices
MAX and MIN — highest and lowest values
GROUP BY — analyzing by category, customer, date
HAVING — filtering aggregated results

What is Aggregation?

Aggregation is the process of summarizing data to get insights. Instead of looking at individual records, you look at groups of records and calculate statistics like totals, averages, and counts.

COUNT

Number of records

SUM

Total of values

AVG

Average value

MAX

Highest value

MIN

Lowest value

GROUP BY

Group records

Aggregation in E-Commerce

# ============================================================
# AGGREGATION EXAMPLES IN E-COMMERCE
# ============================================================

# 1. COUNT - How many products?
SELECT COUNT(*) FROM products;

# 2. SUM - Total revenue
SELECT SUM(total_amount) FROM orders WHERE status = 'completed';

# 3. AVG - Average order value
SELECT AVG(total_amount) FROM orders;

# 4. MAX - Most expensive product
SELECT MAX(price) FROM products;

# 5. MIN - Cheapest product
SELECT MIN(price) FROM products;

# 6. GROUP BY - Sales by category
SELECT category, SUM(price) AS total_sales
FROM products
GROUP BY category;

Key point: Aggregation turns raw data into meaningful business insights.

Quick Check: What is aggregation? (Answer: Summarizing data to get insights like totals, averages, and counts)

COUNT - Counting Records

Counting in E-Commerce

# ============================================================
# COUNT FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. COUNT ALL PRODUCTS
# ============================================================

cursor.execute("SELECT COUNT(*) FROM products")
count = cursor.fetchone()[0]
print(f"Total products: {count}")

# ============================================================
# 2. COUNT ELECTRONICS PRODUCTS
# ============================================================

cursor.execute("SELECT COUNT(*) FROM products WHERE category = 'Electronics'")
count = cursor.fetchone()[0]
print(f"Electronics products: {count}")

# ============================================================
# 3. COUNT CUSTOMERS WITH ORDERS
# ============================================================

cursor.execute("""
    SELECT COUNT(DISTINCT customer_id) 
    FROM orders
""")
count = cursor.fetchone()[0]
print(f"Customers with orders: {count}")

# ============================================================
# 4. COUNT PRODUCTS WITH STOCK
# ============================================================

cursor.execute("SELECT COUNT(*) FROM products WHERE stock_quantity > 0")
count = cursor.fetchone()[0]
print(f"Products in stock: {count}")

cursor.close()
connection.close()

COUNT key points:

COUNT(*) — counts all rows
COUNT(DISTINCT) — counts unique values
COUNT with WHERE — counts matching rows
Useful for metrics — number of products, customers, orders

Quick Check: What does COUNT(DISTINCT customer_id) count? (Answer: The number of unique customers)

SUM - Adding Values

Calculating Totals in E-Commerce

# ============================================================
# SUM FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. TOTAL REVENUE
# ============================================================

cursor.execute("SELECT SUM(total_amount) FROM orders WHERE status = 'completed'")
total = cursor.fetchone()[0] or 0
print(f"Total revenue: ${total}")

# ============================================================
# 2. REVENUE BY CATEGORY
# ============================================================

cursor.execute("""
    SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.category
""")
results = cursor.fetchall()
print("Revenue by category:")
for r in results:
    print(f"  {r[0]}: ${r[1]}")

# ============================================================
# 3. TOTAL UNITS SOLD
# ============================================================

cursor.execute("SELECT SUM(quantity) FROM order_items")
total_units = cursor.fetchone()[0]
print(f"Total units sold: {total_units}")

# ============================================================
# 4. TOTAL CUSTOMER SPENDING
# ============================================================

cursor.execute("""
    SELECT c.first_name, c.last_name, COALESCE(SUM(o.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
""")
results = cursor.fetchall()
print("\nCustomer spending:")
for r in results:
    print(f"  {r[0]} {r[1]}: ${r[2]}")

cursor.close()
connection.close()

SUM key points:

SUM(column) — adds up values
SUM with JOIN — calculates across tables
COALESCE — handles NULL values
Useful for revenue — total sales, category revenue

Quick Check: What does COALESCE(SUM(total_amount), 0) do? (Answer: Returns 0 instead of NULL when there are no orders)

AVG - Average Values

Calculating Averages in E-Commerce

# ============================================================
# AVG FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. AVERAGE ORDER VALUE
# ============================================================

cursor.execute("SELECT AVG(total_amount) FROM orders WHERE status = 'completed'")
avg = cursor.fetchone()[0] or 0
print(f"Average order value: ${avg:.2f}")

# ============================================================
# 2. AVERAGE PRICE BY CATEGORY
# ============================================================

cursor.execute("""
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
""")
results = cursor.fetchall()
print("Average price by category:")
for r in results:
    print(f"  {r[0]}: ${r[1]:.2f}")

# ============================================================
# 3. AVERAGE PRODUCT RATING
# ============================================================

cursor.execute("""
    SELECT p.product_name, AVG(r.rating) AS avg_rating
    FROM products p
    JOIN reviews r ON p.product_id = r.product_id
    GROUP BY p.product_id
""")
results = cursor.fetchall()
print("\nProduct ratings:")
for r in results:
    print(f"  {r[0]}: {r[1]:.1f} stars")

# ============================================================
# 4. AVERAGE QUANTITY PER ORDER
# ============================================================

cursor.execute("""
    SELECT AVG(quantity) AS avg_items
    FROM order_items
""")
avg_items = cursor.fetchone()[0] or 0
print(f"Average items per order: {avg_items:.1f}")

cursor.close()
connection.close()

AVG key points:

AVG(column) — calculates average
AVG with GROUP BY — averages per group
Ignores NULL — NULL values are not included
Useful for benchmarks — average order, rating, price

Quick Check: What does AVG do with NULL values? (Answer: It ignores them)

MAX and MIN

Finding Extremes in E-Commerce

# ============================================================
# MAX AND MIN FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. HIGHEST AND LOWEST PRICES
# ============================================================

cursor.execute("SELECT MAX(price), MIN(price) FROM products")
max_price, min_price = cursor.fetchone()
print(f"Highest price: ${max_price}")
print(f"Lowest price: ${min_price}")

# ============================================================
# 2. MOST EXPENSIVE PRODUCT IN EACH CATEGORY
# ============================================================

cursor.execute("""
    SELECT category, product_name, price
    FROM products
    WHERE (category, price) IN (
        SELECT category, MAX(price)
        FROM products
        GROUP BY category
    )
""")
results = cursor.fetchall()
print("\nMost expensive product in each category:")
for r in results:
    print(f"  {r[0]}: {r[1]} - ${r[2]}")

# ============================================================
# 3. BEST AND WORST SELLER
# ============================================================

cursor.execute("""
    SELECT p.product_name, SUM(oi.quantity) AS total_sold
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id
    ORDER BY total_sold DESC
""")
results = cursor.fetchall()
print("\nProduct sales ranking:")
for i, r in enumerate(results[:5], 1):
    print(f"  #{i}: {r[0]} - {r[1]} units")

# ============================================================
# 4. HIGHEST AND LOWEST RATED PRODUCTS
# ============================================================

cursor.execute("""
    SELECT p.product_name, AVG(r.rating) AS avg_rating
    FROM products p
    JOIN reviews r ON p.product_id = r.product_id
    GROUP BY p.product_id
    HAVING avg_rating = (SELECT MAX(avg_rating) FROM (
        SELECT AVG(rating) AS avg_rating
        FROM reviews
        GROUP BY product_id
    ) AS t)
""")
best = cursor.fetchall()
print(f"\nHighest rated product: {best[0][0]} - {best[0][1]:.1f} stars")

cursor.close()
connection.close()

MAX and MIN key points:

MAX — finds the highest value
MIN — finds the lowest value
Useful for extremes — best sellers, most expensive
Can use with GROUP BY — per category, per customer

Quick Check: What do MAX and MIN find? (Answer: The highest and lowest values)

GROUP BY - Grouping Data

Analyzing Data by Groups

# ============================================================
# GROUP BY FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. SALES BY CATEGORY
# ============================================================

query = """
    SELECT p.category,
           COUNT(DISTINCT o.order_id) AS order_count,
           SUM(oi.quantity) AS units_sold,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY p.category
    ORDER BY revenue DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("Sales by category:")
for r in results:
    print(f"  {r[0]}: {r[1]} orders, {r[2]} units, ${r[3]:.2f}")

# ============================================================
# 2. MONTHLY SALES
# ============================================================

query = """
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
           COUNT(order_id) AS order_count,
           SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
    ORDER BY month DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nMonthly sales:")
for r in results:
    print(f"  {r[0]}: {r[1]} orders, ${r[2]:.2f}")

# ============================================================
# 3. CUSTOMER SPENDING
# ============================================================

query = """
    SELECT c.first_name, c.last_name,
           COUNT(o.order_id) AS order_count,
           COALESCE(SUM(o.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
    ORDER BY total_spent DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nCustomer spending:")
for r in results:
    print(f"  {r[0]} {r[1]}: {r[2]} orders, ${r[3]:.2f}")

cursor.close()
connection.close()

GROUP BY key points:

GROUP BY column — groups by that column
Aggregation functions — used on grouped data
ORDER BY — sort results
Useful for reports — sales by category, monthly revenue

Quick Check: What does GROUP BY do? (Answer: Groups records with the same value so you can apply aggregation functions)

HAVING - Filtering Groups

Filtering After GROUP BY

# ============================================================
# HAVING FROM PYTHON
# ============================================================

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="secret",
    database="ecommerce_db"
)

cursor = connection.cursor()

# ============================================================
# 1. CATEGORIES WITH REVENUE OVER $1000
# ============================================================

query = """
    SELECT p.category,
           SUM(oi.quantity * oi.unit_price) AS revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.category
    HAVING revenue > 1000
    ORDER BY revenue DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("Categories with revenue over $1000:")
for r in results:
    print(f"  {r[0]}: ${r[1]:.2f}")

# ============================================================
# 2. CUSTOMERS WHO SPENT OVER $500
# ============================================================

query = """
    SELECT c.first_name, c.last_name,
           COALESCE(SUM(o.total_amount), 0) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
    HAVING total_spent > 500
    ORDER BY total_spent DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nCustomers who spent over $500:")
for r in results:
    print(f"  {r[0]} {r[1]}: ${r[2]:.2f}")

# ============================================================
# 3. PRODUCTS WITH MORE THAN 5 REVIEWS
# ============================================================

query = """
    SELECT p.product_name,
           COUNT(r.review_id) AS review_count,
           AVG(r.rating) AS avg_rating
    FROM products p
    JOIN reviews r ON p.product_id = r.product_id
    GROUP BY p.product_id
    HAVING review_count > 5
    ORDER BY review_count DESC
"""
cursor.execute(query)
results = cursor.fetchall()

print("\nProducts with more than 5 reviews:")
for r in results:
    print(f"  {r[0]}: {r[1]} reviews, {r[2]:.1f} stars")

cursor.close()
connection.close()

HAVING key points:

HAVING — filters groups after GROUP BY
Uses aggregated values — SUM, AVG, COUNT in condition
Different from WHERE — WHERE filters before grouping
Useful for thresholds — revenue over $1000, customers with >5 orders

Quick Check: What is the difference between WHERE and HAVING? (Answer: WHERE filters rows before grouping; HAVING filters groups after grouping)

E-Commerce Python Examples

Complete E-Commerce Analytics

# ============================================================
# COMPLETE E-COMMERCE ANALYTICS
# ============================================================

import mysql.connector

class ECommerceAnalytics:
    """E-Commerce analytics using aggregation"""
    
    def __init__(self, db_config):
        self.db_config = db_config
        self.connection = None
        self.cursor = None
    
    def connect(self):
        try:
            self.connection = mysql.connector.connect(**self.db_config)
            self.cursor = self.connection.cursor()
            return True
        except Exception as e:
            print(f"Connection failed: {e}")
            return False
    
    # ============================================================
    # DASHBOARD METRICS
    # ============================================================
    
    def get_dashboard_metrics(self):
        """Get all dashboard metrics in one query"""
        query = """
            SELECT 
                (SELECT COUNT(*) FROM customers) AS total_customers,
                (SELECT COUNT(*) FROM products WHERE stock_quantity > 0) AS products_in_stock,
                (SELECT COUNT(*) FROM orders WHERE status = 'pending') AS pending_orders,
                (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE status = 'completed') AS total_revenue,
                (SELECT COALESCE(AVG(total_amount), 0) FROM orders WHERE status = 'completed') AS avg_order_value
        """
        self.cursor.execute(query)
        return self.cursor.fetchone()
    
    # ============================================================
    # SALES REPORTS
    # ============================================================
    
    def get_sales_by_category(self):
        """Get sales grouped by category"""
        query = """
            SELECT p.category,
                   COUNT(DISTINCT o.order_id) AS order_count,
                   SUM(oi.quantity) AS units_sold,
                   SUM(oi.quantity * oi.unit_price) AS revenue,
                   AVG(oi.unit_price) AS avg_price
            FROM products p
            JOIN order_items oi ON p.product_id = oi.product_id
            JOIN orders o ON oi.order_id = o.order_id
            WHERE o.status = 'completed'
            GROUP BY p.category
            ORDER BY revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_monthly_revenue(self, months=6):
        """Get monthly revenue for last N months"""
        query = """
            SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
                   COUNT(order_id) AS order_count,
                   SUM(total_amount) AS revenue,
                   AVG(total_amount) AS avg_order
            FROM orders
            WHERE status = 'completed'
            AND order_date >= DATE_SUB(CURDATE(), INTERVAL %s MONTH)
            GROUP BY DATE_FORMAT(order_date, '%Y-%m')
            ORDER BY month DESC
        """
        self.cursor.execute(query, (months,))
        return self.cursor.fetchall()
    
    # ============================================================
    # CUSTOMER REPORTS
    # ============================================================
    
    def get_customer_lifetime_value(self, min_orders=1):
        """Get customer lifetime value"""
        query = """
            SELECT c.customer_id, c.first_name, c.last_name,
                   COUNT(o.order_id) AS order_count,
                   COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
                   COALESCE(AVG(o.total_amount), 0) AS avg_order_value
            FROM customers c
            LEFT JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id
            HAVING order_count >= %s
            ORDER BY lifetime_value DESC
        """
        self.cursor.execute(query, (min_orders,))
        return self.cursor.fetchall()
    
    def get_top_customers(self, limit=10):
        """Get top customers by spending"""
        query = """
            SELECT c.first_name, c.last_name, c.email,
                   COUNT(o.order_id) AS order_count,
                   COALESCE(SUM(o.total_amount), 0) AS total_spent
            FROM customers c
            LEFT JOIN orders o ON c.customer_id = o.customer_id
            GROUP BY c.customer_id
            ORDER BY total_spent DESC
            LIMIT %s
        """
        self.cursor.execute(query, (limit,))
        return self.cursor.fetchall()
    
    # ============================================================
    # PRODUCT REPORTS
    # ============================================================
    
    def get_product_performance(self):
        """Get product performance metrics"""
        query = """
            SELECT p.product_id, p.product_name, p.category, p.price,
                   COALESCE(SUM(oi.quantity), 0) AS total_sold,
                   COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
                   COALESCE(AVG(r.rating), 0) AS avg_rating,
                   COUNT(DISTINCT r.review_id) AS review_count,
                   p.stock_quantity
            FROM products p
            LEFT JOIN order_items oi ON p.product_id = oi.product_id
            LEFT JOIN reviews r ON p.product_id = r.product_id
            GROUP BY p.product_id
            ORDER BY revenue DESC
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    def get_out_of_stock_products(self):
        """Get products out of stock"""
        query = """
            SELECT product_name, category, stock_quantity
            FROM products
            WHERE stock_quantity = 0
        """
        self.cursor.execute(query)
        return self.cursor.fetchall()
    
    # ============================================================
    # ANALYTICS SUMMARY
    # ============================================================
    
    def get_summary_report(self):
        """Get comprehensive analytics summary"""
        report = {}
        
        # Basic metrics
        self.cursor.execute("SELECT COUNT(*) FROM customers")
        report['total_customers'] = self.cursor.fetchone()[0]
        
        self.cursor.execute("SELECT COUNT(*) FROM products")
        report['total_products'] = self.cursor.fetchone()[0]
        
        self.cursor.execute("SELECT COUNT(*) FROM orders")
        report['total_orders'] = self.cursor.fetchone()[0]
        
        self.cursor.execute("SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE status = 'completed'")
        report['total_revenue'] = self.cursor.fetchone()[0]
        
        self.cursor.execute("SELECT COALESCE(AVG(total_amount), 0) FROM orders WHERE status = 'completed'")
        report['avg_order_value'] = self.cursor.fetchone()[0]
        
        self.cursor.execute("SELECT COUNT(DISTINCT customer_id) FROM orders")
        report['customers_with_orders'] = self.cursor.fetchone()[0]
        
        self.cursor.execute("SELECT COUNT(*) FROM orders WHERE status = 'pending'")
        report['pending_orders'] = self.cursor.fetchone()[0]
        
        return report
    
    def display_results(self, results, title="Results"):
        """Display results"""
        if not results:
            print(f"\n{title}: No results")
            return
        print(f"\n{title}:")
        print("-" * 60)
        if isinstance(results, tuple):
            print(f"  {results}")
        else:
            for row in results:
                print(f"  {row}")
    
    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()

# ============================================================
# DEMONSTRATION
# ============================================================

db_config = {
    "host": "localhost",
    "user": "root",
    "password": "secret",
    "database": "ecommerce_db"
}

analytics = ECommerceAnalytics(db_config)

if analytics.connect():
    
    print("=" * 60)
    print("E-COMMERCE ANALYTICS DASHBOARD")
    print("=" * 60)
    
    # 1. Dashboard metrics
    print("\n1. DASHBOARD METRICS")
    metrics = analytics.get_dashboard_metrics()
    if metrics:
        print(f"  Total Customers: {metrics[0]}")
        print(f"  Products in Stock: {metrics[1]}")
        print(f"  Pending Orders: {metrics[2]}")
        print(f"  Total Revenue: ${metrics[3]:.2f}")
        print(f"  Average Order Value: ${metrics[4]:.2f}")
    
    # 2. Sales by category
    print("\n2. SALES BY CATEGORY")
    results = analytics.get_sales_by_category()
    for r in results:
        print(f"  {r[0]}: {r[1]} orders, {r[2]} units, ${r[3]:.2f}")
    
    # 3. Monthly revenue
    print("\n3. MONTHLY REVENUE")
    results = analytics.get_monthly_revenue(3)
    for r in results:
        print(f"  {r[0]}: {r[1]} orders, ${r[2]:.2f} (avg ${r[3]:.2f})")
    
    # 4. Top customers
    print("\n4. TOP CUSTOMERS")
    results = analytics.get_top_customers(3)
    for r in results:
        print(f"  {r[0]} {r[1]}: {r[2]} orders, ${r[3]:.2f}")
    
    # 5. Summary report
    print("\n5. SUMMARY REPORT")
    report = analytics.get_summary_report()
    for key, value in report.items():
        print(f"  {key}: {value}")
    
    analytics.close()

This analytics example shows:

Dashboard metrics with multiple aggregations
Sales analysis by category and month
Customer lifetime value calculations
Product performance tracking
Comprehensive business reports

Quick Check: What is customer lifetime value? (Answer: The total amount a customer has spent over their entire relationship with the business)

Best Practices

Aggregation Best Practices

# ============================================================
# AGGREGATION BEST PRACTICES
# ============================================================

print("1. USE ALIASES FOR AGGREGATED COLUMNS")
print("   - SUM(total) AS total_revenue")
print("   - AVG(price) AS avg_price")
print("   - Improves readability")

print("\n2. HANDLE NULL VALUES")
print("   - Use COALESCE(SUM(amount), 0)")
print("   - NULL + number = NULL")

print("\n3. FILTER BEFORE GROUPING")
print("   - Use WHERE to filter rows")
print("   - Reduces data to aggregate")

print("\n4. USE HAVING FOR GROUP FILTERS")
print("   - Filter groups after aggregation")
print("   - HAVING SUM(amount) > 1000")

print("\n5. USE DISTINCT WHEN NEEDED")
print("   - COUNT(DISTINCT customer_id)")
print("   - Counts unique values")

print("\n6. OPTIMIZE WITH INDEXES")
print("   - Index columns used in GROUP BY")
print("   - Speeds up aggregation")

print("\n7. USE DATE FUNCTIONS WISELY")
print("   - DATE_FORMAT() for grouping by month")
print("   - GROUP BY DATE(order_date)")

print("\n8. TEST WITH EXPLAIN")
print("   - Check execution plans")
print("   - Identify performance issues")

Summary of best practices:

Use aliases — for readability
Handle NULL — use COALESCE
Filter before grouping — use WHERE
Use HAVING for groups — filter after aggregation
Use DISTINCT — for unique counts

Quick Check: What is the difference between WHERE and HAVING in aggregation? (Answer: WHERE filters rows before grouping; HAVING filters groups after grouping)

Try It Yourself

Experiment with aggregation functions in the editor below.

Loading Pyodide... 0%

Python Code Editor

# Python MySQL Aggregation - Practice

print("=" * 40)
print("AGGREGATION - PRACTICE")
print("=" * 40)

# Simulated E-Commerce Data
class ECommerceDB:
    products = [
        {"id": 1, "name": "iPhone 15 Pro", "category": "Electronics", "price": 999.99},
        {"id": 2, "name": "MacBook Air", "category": "Electronics", "price": 1099.99},
        {"id": 3, "name": "Nike Air Max", "category": "Footwear", "price": 149.99},
        {"id": 4, "name": "Adidas Ultraboost", "category": "Footwear", "price": 179.99},
        {"id": 5, "name": "Levi's Jeans", "category": "Clothing", "price": 69.99},
        {"id": 6, "name": "Nike T-Shirt", "category": "Clothing", "price": 29.99}
    ]
    
    orders = [
        {"id": 1, "customer_id": 1, "total": 1099.98, "status": "completed"},
        {"id": 2, "customer_id": 1, "total": 69.99, "status": "pending"},
        {"id": 3, "customer_id": 2, "total": 179.99, "status": "completed"},
        {"id": 4, "customer_id": 3, "total": 149.99, "status": "completed"}
    ]
    
    @classmethod
    def count_products(cls):
        return len(cls.products)
    
    @classmethod
    def count_by_category(cls):
        categories = {}
        for p in cls.products:
            categories[p["category"]] = categories.get(p["category"], 0) + 1
        return categories
    
    @classmethod
    def sum_prices(cls):
        return sum(p["price"] for p in cls.products)
    
    @classmethod
    def avg_price(cls):
        return sum(p["price"] for p in cls.products) / len(cls.products)
    
    @classmethod
    def max_price(cls):
        return max(p["price"] for p in cls.products)
    
    @classmethod
    def min_price(cls):
        return min(p["price"] for p in cls.products)
    
    @classmethod
    def sum_revenue(cls):
        return sum(o["total"] for o in cls.orders if o["status"] == "completed")

print("\n1. BASIC AGGREGATION")
print("-" * 40)

print(f"Total Products: {ECommerceDB.count_products()}")
print(f"Total Revenue: ${ECommerceDB.sum_revenue():.2f}")
print(f"Average Price: ${ECommerceDB.avg_price():.2f}")
print(f"Highest Price: ${ECommerceDB.max_price():.2f}")
print(f"Lowest Price: ${ECommerceDB.min_price():.2f}")

print("\n2. GROUP BY - Products by Category")
print("-" * 40)

category_counts = ECommerceDB.count_by_category()
for cat, count in category_counts.items():
    print(f"  {cat}: {count} products")

print("\n3. COMPLETE ANALYSIS")
print("-" * 40)
print("""
┌─────────────────┬──────────────────────────────────────────────┐
│ Function        │ Result                                     │
├─────────────────┼──────────────────────────────────────────────┤
│ COUNT           │ Number of records                          │
│ SUM             │ Total value                                │
│ AVG             │ Average value                              │
│ MAX             │ Highest value                              │
│ MIN             │ Lowest value                               │
│ GROUP BY        │ Group by category, customer, date         │
│ HAVING          │ Filter groups after aggregation            │
└─────────────────┴──────────────────────────────────────────────┘
""")

print("Aggregation turns data into insights!")

========================================
AGGREGATION - PRACTICE
========================================

1. BASIC AGGREGATION
----------------------------------------
Total Products: 6
Total Revenue: $1429.96
Average Price: $421.49
Highest Price: $1099.99
Lowest Price: $29.99

2. GROUP BY - Products by Category
----------------------------------------
Electronics: 2 products
Footwear: 2 products
Clothing: 2 products

3. COMPLETE ANALYSIS
----------------------------------------
┌─────────────────┬──────────────────────────────────────────────┐
│ Function │ Result │
├─────────────────┼──────────────────────────────────────────────┤
│ COUNT │ Number of records │
│ SUM │ Total value │
│ AVG │ Average value │
│ MAX │ Highest value │
│ MIN │ Lowest value │
│ GROUP BY │ Group by category, customer, date │
│ HAVING │ Filter groups after aggregation │
└─────────────────┴──────────────────────────────────────────────┘

Aggregation turns data into insights!

🏆

You've Got It!

You now understand aggregation in MySQL from Python. You can use COUNT, SUM, AVG, MAX, MIN, GROUP BY, and HAVING for E-Commerce analytics.

Quick Quiz

Test what you've learned:

1. What does COUNT(*) do?

A) Counts only non-null values B) Counts all rows C) Counts distinct values D) Counts rows with non-null values

2. What does SUM() do?

A) Finds the highest value B) Adds up all values C) Calculates the average D) Counts the number of rows

3. What does GROUP BY do?

A) Sorts the results B) Groups rows with the same values C) Filters rows D) Joins tables

4. What is the difference between WHERE and HAVING?

A) They are the same B) WHERE filters rows, HAVING filters groups C) WHERE filters groups, HAVING filters rows D) WHERE is used with GROUP BY, HAVING without

5. Which function finds the highest value?

A) MIN B) MAX C) AVG D) SUM

Frequently Asked Questions

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows, including those with NULL values. COUNT(column) counts only non-null values in that specific column.

Can I use multiple aggregation functions together?

Yes, you can use multiple aggregation functions in one SELECT statement. For example: SELECT COUNT(*), SUM(amount), AVG(price) FROM sales

What is a common interview question about aggregation?

Common questions include: "Explain the difference between WHERE and HAVING," "What is the difference between COUNT(*) and COUNT(column)?" and "How do you group data in MySQL?"

How does AVG() handle NULL values?

AVG() ignores NULL values when calculating the average. It only considers non-null values in the calculation.

Will this conflict with future pure MySQL tutorials?

No. This tutorial focuses on executing aggregation from Python with mysql-connector. Future MySQL tutorials will focus on SQL syntax and optimization with different titles and URLs.

Where to Go From Here

Now that you understand aggregation, check out these related topics:

Previous: JOIN Operations Next: Case Study →

Python

Python: Aggregation in MySQL

What is Aggregation?

COUNT

SUM

AVG

MAX

MIN

GROUP BY

Aggregation in E-Commerce

COUNT - Counting Records

Counting in E-Commerce

SUM - Adding Values

Calculating Totals in E-Commerce

AVG - Average Values

Calculating Averages in E-Commerce

MAX and MIN

Finding Extremes in E-Commerce

GROUP BY - Grouping Data

Analyzing Data by Groups

HAVING - Filtering Groups

Filtering After GROUP BY

E-Commerce Python Examples

Complete E-Commerce Analytics

Best Practices

Aggregation Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between COUNT(*) and COUNT(column)?

Can I use multiple aggregation functions together?

What is a common interview question about aggregation?

How does AVG() handle NULL values?

Will this conflict with future pure MySQL tutorials?

Where to Go From Here

Case Study

Practice Assignments

JOIN Operations

Python

Python: Aggregation in MySQL

What is Aggregation?

COUNT

SUM

AVG

MAX

MIN

GROUP BY

Aggregation in E-Commerce

COUNT - Counting Records

Counting in E-Commerce

SUM - Adding Values

Calculating Totals in E-Commerce

AVG - Average Values

Calculating Averages in E-Commerce

MAX and MIN

Finding Extremes in E-Commerce

GROUP BY - Grouping Data

Analyzing Data by Groups

HAVING - Filtering Groups

Filtering After GROUP BY

E-Commerce Python Examples

Complete E-Commerce Analytics

Best Practices

Aggregation Best Practices

Try It Yourself

You've Got It!

Quick Quiz

Frequently Asked Questions

What is the difference between COUNT(*) and COUNT(column)? ▼

Can I use multiple aggregation functions together? ▼

What is a common interview question about aggregation? ▼

How does AVG() handle NULL values? ▼

Will this conflict with future pure MySQL tutorials? ▼

Where to Go From Here

Case Study

Practice Assignments

JOIN Operations

What is the difference between COUNT(*) and COUNT(column)?

Can I use multiple aggregation functions together?

What is a common interview question about aggregation?

How does AVG() handle NULL values?

Will this conflict with future pure MySQL tutorials?