SQL COUNT

-- Total rows in the table
SELECT COUNT(*) FROM users;

-- Rows matching a filter
SELECT COUNT(*) FROM users WHERE country = 'AU';

-- COUNT(column) ignores NULL
SELECT COUNT(email)  AS rows_with_email,
       COUNT(*)      AS total_rows,
       COUNT(*) - COUNT(email) AS rows_without_email
FROM users;

-- COUNT(DISTINCT ...) - unique values
SELECT COUNT(DISTINCT country) AS unique_countries FROM users;

-- Grouped counts
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
ORDER BY users DESC;

-- Multi-group
SELECT country, tier, COUNT(*) AS users
FROM users
GROUP BY country, tier;

-- Filtered count with FILTER (Postgres) - cleaner than CASE WHEN
SELECT
  COUNT(*) FILTER (WHERE status = 'paid')    AS paid_orders,
  COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders
FROM orders;

-- Or using CASE WHEN inside SUM (portable)
SELECT
  SUM(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;

-- Big tables: COUNT(*) can be slow on Postgres
-- For approximate counts, query the catalog:
SELECT reltuples::BIGINT AS estimate
FROM pg_class
WHERE relname = 'users';

-- Counting + paging in one round-trip
SELECT
  COUNT(*) OVER ()  AS total_rows,
  id, name, email
FROM users
ORDER BY id
LIMIT 20 OFFSET 0;
-- total_rows is repeated on every row but you only need one query.

-- Cardinality matters for indexes
-- COUNT can use a covering index if you only select indexed columns.

SELECT COUNT(*) AS total_customers
FROM customers;