Aggregate Functions

-- How many products do we have?
SELECT COUNT(*) AS total_products
FROM products;

-- How many Electronics products?
SELECT COUNT(*) AS electronics_count
FROM products
WHERE category = 'Electronics';

-- How many products have a discount?
SELECT COUNT(discount) AS products_with_discount
FROM products_with_discount;

-- Compare COUNT(*) vs COUNT(column)
SELECT
  COUNT(*) AS total_rows,
  COUNT(discount) AS rows_with_discount,
  COUNT(*) - COUNT(discount) AS rows_without_discount
FROM products_with_discount;

-- How many different categories?
SELECT COUNT(DISTINCT category) AS category_count
FROM products;

SELECT
  COUNT(category) AS total_categories,
  COUNT(DISTINCT category) AS unique_categories
FROM products;

-- Total value of all inventory
SELECT SUM(price * stock) AS total_inventory_value
FROM products;

-- Total stock quantity for Electronics
SELECT SUM(stock) AS total_electronics_stock
FROM products
WHERE category = 'Electronics';

-- Sum of discounts (NULLs are skipped)
SELECT SUM(discount) AS total_discounts
FROM products_with_discount;

-- Average product price
SELECT AVG(price) AS average_price
FROM products;

-- Average price of Furniture
SELECT AVG(price) AS avg_furniture_price
FROM products
WHERE category = 'Furniture';

-- Average discount (only counts non-NULL)
SELECT AVG(discount) AS avg_discount
FROM products_with_discount;

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

-- First and last product alphabetically
SELECT
  MIN(product_name) AS first_alphabetically,
  MAX(product_name) AS last_alphabetically
FROM products;

-- Earliest and most recent hire dates
SELECT
  MIN(hire_date) AS oldest_employee,
  MAX(hire_date) AS newest_employee
FROM employees;

-- Salary range for Engineering department
SELECT
  MIN(salary) AS min_eng_salary,
  MAX(salary) AS max_eng_salary
FROM employees
WHERE department = 'Engineering';

-- Complete product statistics
SELECT
  COUNT(*) AS total_products,
  SUM(stock) AS total_units,
  AVG(price) AS avg_price,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive,
  SUM(price * stock) AS total_value
FROM products;

-- Engineering department salary stats
SELECT
  COUNT(*) AS employees,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  AVG(salary) AS avg_salary,
  SUM(salary) AS total_payroll
FROM employees
WHERE department = 'Engineering';

-- Total revenue if we sold all inventory
SELECT
  SUM(price * stock) AS potential_revenue,
  AVG(price * stock) AS avg_product_value
FROM products;

-- Annual salary costs
SELECT
  SUM(salary * 12) AS annual_payroll,
  AVG(salary * 12) AS avg_annual_salary
FROM employees;

-- Round average price to 2 decimal places
SELECT
  ROUND(AVG(price), 2) AS avg_price,
  ROUND(AVG(stock), 0) AS avg_stock
FROM products;

-- No products in "Clothing" category
SELECT
  COUNT(*) AS count,
  SUM(price) AS sum_price,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM products
WHERE category = 'Clothing';

-- Overall inventory statistics
SELECT
  COUNT(*) AS total_products,
  COUNT(DISTINCT category) AS categories,
  SUM(stock) AS total_units_in_stock,
  ROUND(AVG(price), 2) AS avg_price,
  MIN(stock) AS lowest_stock,
  MAX(stock) AS highest_stock
FROM products
WHERE stock > 0;

-- Order summary
SELECT
  COUNT(*) AS total_orders,
  SUM(quantity) AS total_items_sold,
  ROUND(AVG(quantity), 2) AS avg_items_per_order,
  MIN(quantity) AS min_order_size,
  MAX(quantity) AS max_order_size
FROM orders;

-- Company-wide salary statistics
SELECT
  COUNT(*) AS total_employees,
  ROUND(AVG(salary), 2) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  MAX(salary) - MIN(salary) AS salary_range
FROM employees;