*************

SQL code: Format Clear

-- 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
;

SQL version:

Stuck with a problem? Need help? Ask professionals on our Telegram channel!

Join the channel

Your quersion is posted.
Join our Telegram channel and get answers.

SQLize Online / PHPize Online

A A A

Donate Popular

Donate

Popular

SQLize Online / PHPize Online A A A

Donate Popular Donate Popular

SQLize Online / PHPize Online

A A A

Donate Popular

Donate

Popular