COUNT, SUM

-- COUNT variations
COUNT(*)           -- count all rows (including NULLs)
COUNT(column)      -- count non-NULL values in column
COUNT(DISTINCT col) -- count unique non-NULL values

-- SUM
SUM(amount)        -- total of all non-NULL values
SUM(DISTINCT amt)  -- sum of unique values only

-- With WHERE
SELECT COUNT(*) FROM orders WHERE status = 'completed';
SELECT SUM(total_amount) FROM orders WHERE status = 'completed';

-- Conditional aggregation (CASE inside aggregate)
SELECT
  COUNT(*)                                               AS total_orders,
  COUNT(CASE WHEN status = 'completed' THEN 1 END)      AS completed,
  COUNT(CASE WHEN status = 'cancelled' THEN 1 END)      AS cancelled,
  SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS revenue
FROM orders;

-- COUNT(*) vs COUNT(1) — same performance, same result

-- Daily operations dashboard
SELECT
  DATE(o.created_at)                                      AS date,
  COUNT(*)                                                AS total_orders,
  COUNT(DISTINCT o.user_id)                               AS unique_customers,
  COUNT(CASE WHEN o.status = 'completed' THEN 1 END)     AS completed,
  COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END)     AS cancelled,
  COUNT(CASE WHEN o.status = 'refunded' THEN 1 END)      AS refunded,
  SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS gross_revenue,
  SUM(CASE WHEN o.status = 'refunded' THEN o.total_amount ELSE 0 END)  AS refunds,
  SUM(o.total_amount) FILTER (WHERE o.status = 'completed')             AS net_revenue,  -- PostgreSQL
  COUNT(DISTINCT oi.product_id)                           AS unique_products_sold
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(o.created_at)
ORDER BY date DESC;