SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE store ( ID int not null auto_increment, CITY varchar(500) not null, PRIMARY KEY (ID) ); INSERT INTO store VALUES (1, 'Moscow'), (2, 'London'); CREATE TABLE book ( ID int not null auto_increment, NAME varchar(500) not null, ISSUE_YEAR YEAR, PUBLISHER_ID int, ISBN char(17), PRIMARY KEY (ID) ); INSERT INTO book (NAME) VALUES ('SuperBook 1'), ('Very Interesting Book'), ('Just one more book'); CREATE TABLE book_store ( BOOK_ID int not null, STORE_ID int not null, PRICE DECIMAL(10, 2), QUANTITY int unsigned not null default 0, PRIMARY KEY (BOOK_ID, STORE_ID), INDEX IX_BOOK (BOOK_ID), FOREIGN KEY FK_BOOK_STORE_BOOK (BOOK_ID) references book(ID) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY FK_BOOK_STORE_BOOK (STORE_ID) references store(ID) ON UPDATE RESTRICT ON DELETE RESTRICT ); INSERT INTO book_store VALUES (1, 1, 100, 2), (1, 2, 100, 2), (2, 1, 100, 2), (3, 2, 100, 2); SELECT book.NAME, SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)) AS CITY1_QUANTITY, SUM(IF(store.CITY = 'London', QUANTITY, 0)) AS CITY2_QUANTITY, SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)- IF(store.CITY = 'London', QUANTITY, 0)) AS DIFF_QUANTITY FROM book LEFT JOIN book_store ON book_store.BOOK_ID = book.ID JOIN store ON store.ID = book_store.STORE_ID WHERE store.CITY IN ('Moscow', 'London') GROUP BY book.NAME;