Aggregate Functions

-- COUNT(*) — counts every row regardless of NULL values in any column
SELECT COUNT(*) AS total_customers
FROM   customers;

-- COUNT(column) — counts only rows where the column is NOT NULL
-- phone is nullable — this returns fewer rows than COUNT(*)
SELECT COUNT(*)          AS total_customers,
       COUNT(phone)      AS customers_with_phone,
       COUNT(city)       AS customers_with_city
FROM   customers;

-- COUNT(DISTINCT column) — counts unique non-null values
SELECT COUNT(DISTINCT country)      AS unique_countries,
       COUNT(DISTINCT loyalty_tier) AS unique_tiers
FROM   customers;

-- COUNT with a WHERE clause — counts matching rows only
SELECT COUNT(*) AS pending_orders
FROM   orders
WHERE  status = 'pending';

-- COUNT across multiple tables — how many products have been ordered at least once
SELECT COUNT(DISTINCT product_id) AS ordered_products
FROM   order_items;

-- Basic SUM and AVG on orders
SELECT SUM(total_amount)            AS total_revenue,
       AVG(total_amount)            AS avg_order_value,
       ROUND(AVG(total_amount), 2)  AS avg_order_rounded
FROM   orders;

-- SUM and AVG on products
SELECT category,
       SUM(stock_qty)               AS total_stock,
       SUM(unit_price * stock_qty)  AS total_stock_value,
       ROUND(AVG(unit_price), 2)    AS avg_price
FROM   products
WHERE  active_flag = 'Y'
GROUP BY category
ORDER BY total_stock_value DESC;

-- Demonstrating NULL behaviour in AVG
-- AVG ignores NULLs — divides by count of non-null rows, not total rows
SELECT COUNT(*)                     AS total_rows,
       COUNT(phone)                 AS non_null_phone,
       AVG(LENGTH(phone))           AS avg_phone_length,
       SUM(LENGTH(phone)) /
           COUNT(*)                 AS avg_incl_nulls_as_zero
FROM   customers;

-- SUM with NVL — treat NULL as zero before summing
SELECT SUM(NVL(stock_qty, 0)) AS total_stock_treating_null_as_zero
FROM   products;

-- MIN and MAX on numeric columns
SELECT MIN(unit_price)  AS cheapest_product,
       MAX(unit_price)  AS most_expensive,
       MAX(unit_price) - MIN(unit_price) AS price_range
FROM   products
WHERE  active_flag = 'Y';

-- MIN and MAX on date columns — earliest and latest order
SELECT MIN(order_date)  AS first_order,
       MAX(order_date)  AS latest_order,
       MAX(order_date) - MIN(order_date) AS days_of_trading
FROM   orders;

-- MIN and MAX on character columns — alphabetical ordering
SELECT MIN(full_name)  AS first_alphabetically,
       MAX(full_name)  AS last_alphabetically
FROM   customers;

-- MIN and MAX in the HR schema — salary range per job
SELECT j.job_title,
       MIN(s.amount)           AS min_salary,
       MAX(s.amount)           AS max_salary,
       MAX(s.amount) -
           MIN(s.amount)       AS salary_spread
FROM   employees  e
JOIN   jobs       j ON j.job_id      = e.job_id
JOIN   salaries   s ON s.employee_id = e.employee_id
WHERE  s.salary_type = 'monthly'
GROUP BY j.job_title
ORDER BY max_salary DESC;

-- Demonstrate all aggregate behaviours on a column with NULLs
-- payments.notes is fully NULL in the dataset
SELECT COUNT(*)             AS count_star,
       COUNT(amount)        AS count_amount,
       SUM(amount)          AS sum_amount,
       AVG(amount)          AS avg_amount,
       MIN(amount)          AS min_amount,
       MAX(amount)          AS max_amount
FROM   payments
WHERE  order_id = 5001;

-- When every value is NULL — SUM, AVG, MIN, MAX all return NULL
SELECT SUM(phone)   AS sum_nulls,
       AVG(phone)   AS avg_nulls,
       MIN(phone)   AS min_nulls,
       MAX(phone)   AS max_nulls,
       COUNT(phone) AS count_nulls
FROM   customers
WHERE  phone IS NULL;

-- Safe pattern — NVL wraps protect against NULL aggregate results
SELECT NVL(SUM(total_amount), 0)           AS safe_sum,
       NVL(ROUND(AVG(total_amount), 2), 0) AS safe_avg,
       NVL(MIN(total_amount), 0)           AS safe_min,
       NVL(MAX(total_amount), 0)           AS safe_max
FROM   orders
WHERE  status = 'nonexistent';

-- Full order summary in one query
SELECT COUNT(*)                             AS total_orders,
       COUNT(DISTINCT customer_id)          AS unique_customers,
       SUM(total_amount)                    AS total_revenue,
       ROUND(AVG(total_amount), 2)          AS avg_order_value,
       MIN(total_amount)                    AS smallest_order,
       MAX(total_amount)                    AS largest_order,
       MAX(total_amount) - MIN(total_amount) AS order_value_range
FROM   orders;

-- HR summary — headcount, payroll cost, and salary range per department
SELECT d.department_name,
       COUNT(e.employee_id)          AS headcount,
       SUM(s.amount)                 AS monthly_payroll,
       ROUND(AVG(s.amount), 2)       AS avg_salary,
       MIN(s.amount)                 AS lowest_salary,
       MAX(s.amount)                 AS highest_salary
FROM   departments  d
JOIN   employees    e ON e.department_id = d.department_id
JOIN   salaries     s ON s.employee_id   = e.employee_id
WHERE  s.salary_type = 'monthly'
GROUP BY d.department_name
ORDER BY monthly_payroll DESC;

-- Product summary — stock value and pricing statistics per category
SELECT category,
       COUNT(*)                          AS product_count,
       SUM(stock_qty)                    AS units_in_stock,
       ROUND(AVG(unit_price), 2)         AS avg_price,
       MIN(unit_price)                   AS lowest_price,
       MAX(unit_price)                   AS highest_price,
       ROUND(SUM(unit_price * stock_qty), 2) AS total_inventory_value
FROM   products
GROUP BY category
ORDER BY total_inventory_value DESC;