SQL Basics

SQL Functions and Aggregation

SQL offers a variety of built-in functions that allow you to perform calculations and manipulate data.

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value.

• COUNT() - Returns the number of rows
• SUM() - Returns the sum of a numeric column
• AVG() - Returns the average value of a numeric column
• MIN() - Returns the smallest value in a column
• MAX() - Returns the largest value in a column

Examples of Aggregate Functions

-- Count total number of users
SELECT COUNT(*) FROM users;

-- Count active users
SELECT COUNT(*) FROM users WHERE is_active = TRUE;

-- Calculate total order value
SELECT SUM(amount) FROM orders WHERE status = 'Completed';

-- Find average product price
SELECT AVG(price) FROM products WHERE category = 'Electronics';

-- Find most expensive and least expensive products
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive 
FROM products;

-- Statistics by category
SELECT category, 
    COUNT(*) AS product_count, 
    AVG(price) AS avg_price, 
    MIN(price) AS min_price, 
    MAX(price) AS max_price
FROM products
GROUP BY category;
            

Real-world analogy: Aggregate functions are like asking for a summary of your bank account activity. Instead of seeing each transaction, you might want to know the total amount spent, the average daily expenses, or the largest single purchase.

GROUP BY Clause

The GROUP BY clause groups rows with the same values in specified columns, typically used with aggregate functions.

-- Count users by signup month
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    COUNT(*) AS new_users
FROM users
GROUP BY EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)
ORDER BY year, month;

-- Calculate average order value by customer
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY avg_order_value DESC;
            

HAVING Clause

The HAVING clause filters the results of a GROUP BY based on an aggregate function condition. It's like a WHERE clause but for grouped results.

-- Find customers who've placed more than 5 orders
SELECT 
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

-- Find product categories with average price above $100
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;