SELECT
a.id AS account_id,
COALESCE(a.username, 'Unknown') AS username,
COALESCE(SUM(r.amount), 0) AS total_amount,
COALESCE(SUM(t.cost), 0) AS total_cost,
CASE
WHEN COALESCE(SUM(r.amount), 0) BETWEEN 0 AND 200 THEN 'Range 1: 0-200'
WHEN COALESCE(SUM(r.amount), 0) BETWEEN 201 AND 499 THEN 'Range 2: 201-499'
WHEN COALESCE(SUM(r.amount), 0) >= 500 THEN 'Range 3: 500+'
ELSE 'Other Range'
END AS amount_range
FROM
readings r
CROSS JOIN
tariff t
LEFT JOIN
account a ON r.account_id = a.id
GROUP BY
a.id;