SQLize Online / PHPize Online  /  SQLtest Online

-- drop table accounts ; -- REVANTH KOTA WORKSPACE CREATE TABLE IF NOT EXISTS accounts ( id INT PRIMARY KEY, -- Account ID username VARCHAR(255), -- Account username email VARCHAR(255) -- Account email address ); -- drop table tariffs; -- Create the tariffs table CREATE TABLE IF NOT EXISTS tariffs ( id INT PRIMARY KEY, -- Tariff ID name ENUM('A', 'B', 'C', 'D', 'E'), -- Tariff name cost DECIMAL(4,3) -- Tariff cost ); -- drop table readings; -- Create the readings table CREATE TABLE IF NOT EXISTS readings ( account_id INT, -- Account ID tariff_id INT, -- Tariff ID amount SMALLINT, -- Readings amount FOREIGN KEY (account_id) REFERENCES accounts(id), -- Foreign key linking to accounts table FOREIGN KEY (tariff_id) REFERENCES tariffs(id) -- Foreign key linking to tariffs table ); INSERT INTO tariffs (id, name, cost) VALUES (1, 'A', 0.010), (2, 'B', 0.020), (3, 'C', 0.050), (4, 'D', 0.075), (5, 'E', 0.100); INSERT INTO accounts (id, username, email) VALUES (1, 'hshillabeare0', 'rcalkin0@sourceforge.net'), (2, 'sdandy1', 'agaule1@businessweek.com'), (3, 'sgreiswood2', 'toppy2@lulu.com'); INSERT INTO readings (account_id, tariff_id, amount) VALUES (1, 2, 54), (1, 3, 19), (1, 3, 37), (1, 3, 89), (1, 3, 119), (2, 1, 12), (2, 1, 44), (2, 1, 81), (2, 2, 60), (2, 2, 164), (2, 2, 199), (2, 3, 79), (2, 5, 186), (3, 1, 31), (3, 1, 59), (3, 1, 77), (3, 1, 95), (3, 1, 110), (3, 1, 125), (3, 2, 31); /* SELECT a.username, a.email, t_highest.name AS highest_tariff, SUM(r.amount) AS consumption, ROUND(SUM(r.amount * t.cost), 2) AS total_cost FROM accounts a JOIN readings r ON a.id = r.account_id JOIN tariffs t ON r.tariff_id = t.id JOIN ( SELECT r.account_id, MAX(t.cost) AS max_cost FROM readings r JOIN tariffs t ON r.tariff_id = t.id GROUP BY r.account_id ) highest_tariff ON highest_tariff.account_id = a.id AND highest_tariff.max_cost = t.cost JOIN tariffs t_highest ON highest_tariff.max_cost = t_highest.cost GROUP BY a.username, a.email, t_highest.name ORDER BY a.username; SELECT d_table.username, d_table.email, MAX(d_table.highest_tariff) SUM(d_table.consumption) as consumption, SUM(d_table.total_cost) as total_cost FROM ( SELECT a.username, a.email, t_highest.name AS highest_tariff, SUM(r.amount) AS consumption, ROUND(SUM(r.amount * t.cost), 2) AS total_cost FROM accounts a JOIN readings r ON a.id = r.account_id JOIN tariffs t ON r.tariff_id = t.id JOIN ( SELECT r.account_id, t.id AS tariff_id, -- Include tariff_id to avoid ambiguity MAX(t.cost) AS max_cost FROM readings r JOIN tariffs t ON r.tariff_id = t.id GROUP BY r.account_id, t.id ) highest_tariff ON highest_tariff.account_id = a.id AND highest_tariff.max_cost = t.cost AND highest_tariff.tariff_id = t.id -- Ensure correct mapping JOIN tariffs t_highest ON t_highest.id = highest_tariff.tariff_id GROUP BY a.username, a.email, t_highest.name ORDER BY a.username; ) as d_table; */ SELECT d_table.username, d_table.email, MAX(d_table.highest_tariff) AS highest_tariff, -- Added alias and fixed MAX() usage SUM(d_table.consumption) AS consumption, -- Added comma and alias SUM(d_table.total_cost) AS total_cost -- Added alias FROM ( SELECT a.username, a.email, t_highest.name AS highest_tariff, SUM(r.amount) AS consumption, ROUND(SUM(r.amount * t.cost), 2) AS total_cost FROM accounts a JOIN readings r ON a.id = r.account_id JOIN tariffs t ON r.tariff_id = t.id JOIN ( SELECT r.account_id, t.id AS tariff_id, -- Include tariff_id to avoid ambiguity MAX(t.cost) AS max_cost FROM readings r JOIN tariffs t ON r.tariff_id = t.id GROUP BY r.account_id, t.id -- Ensure correct grouping ) highest_tariff ON highest_tariff.account_id = a.id AND highest_tariff.max_cost = t.cost AND highest_tariff.tariff_id = t.id -- Ensure correct mapping JOIN tariffs t_highest ON t_highest.id = highest_tariff.tariff_id GROUP BY a.username, a.email, t_highest.name -- Group by all selected columns ORDER BY a.username ) AS d_table -- Added alias for the derived table GROUP BY d_table.username, d_table.email; -- Group by outer query columns