SQLize | PHPize | SQLtest

-- Hint: use Ctrl+Enter for SQL autocomplete CREATE TABLE currencies ( id CHAR(3) PRIMARY KEY, rate DECIMAL(12,6) ); INSERT INTO currencies VALUES ('RUB', 1), ('USD', 75.64), ('EUR', 91.67); CREATE TABLE clients ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) ); INSERT INTO clients (name) VALUES ('CLient 1'), ('CLient 2'); CREATE TABLE deposits ( num INT PRIMARY KEY, client_id INT, balance DECIMAL(12,2), currency CHAR(3) ); INSERT INTO deposits VALUES (342344, 1, 100, 'RUB'), (342345, 1, 100, 'USD'), (342346, 2, 100, 'EUR'); SELECT * FROM clients JOIN deposits d ON d.client_id = clients.id JOIN currencies cur on d.currency = cur.id ; SELECT clients.name, SUM(balance*rate) as rub_balance FROM clients JOIN deposits d ON d.client_id = clients.id JOIN currencies cur on d.currency = cur.id GROUP BY clients.id, clients.name ; SELECT clients.name, SUM(balance * CASE d.currency WHEN 'USD' THEN 75.64 WHEN 'EUR' THEN 91.67 ELSE 1 END ) as rub_balance FROM clients JOIN deposits d ON d.client_id = clients.id GROUP BY clients.id, clients.name ;