Bits of .NET

📊 Aggregate Functions = Data Insights

Need totals, averages, counts? Aggregate functions summarize data. Essential for reporting and analytics.

📝 Basic Aggregates

SELECT 
    COUNT(*) AS total_rows,
    COUNT(DISTINCT column) AS distinct_values,
    SUM(amount) AS total_sum,
    AVG(amount) AS average,
    MIN(amount) AS min_value,
    MAX(amount) AS max_value,
    STDDEV(amount) AS std_dev
FROM orders;

-- With GROUP BY
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent,
    AVG(total) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

-- With HAVING (filter groups)
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5 AND SUM(total) > 1000;

🎯 Analytical Queries

-- Monthly sales summary
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue,
    AVG(total) AS avg_order_value,
    MIN(total) AS min_order,
    MAX(total) AS max_order
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Product performance
SELECT 
    p.name AS product_name,
    COUNT(oi.product_id) AS times_ordered,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
HAVING COUNT(oi.product_id) > 0
ORDER BY total_revenue DESC;

-- Customer segmentation
SELECT 
    CASE 
        WHEN total_spent > 10000 THEN 'Premium'
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 100 THEN 'Regular'
        ELSE 'Basic'
    END AS segment,
    COUNT(*) AS customer_count,
    SUM(total_spent) AS total_revenue,
    AVG(total_spent) AS avg_spent
FROM (
    SELECT 
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_spending
GROUP BY segment;

💡 Aggregate Tips

• COUNT(*) counts all rows
• COUNT(column) counts non-null values
• DISTINCT removes duplicates
• NULL values are ignored in AVG, SUM, etc.
• Use HAVING for group filtering