For each individual book, information on the number of copies sold in the following two years should be provided

[elysha]

Skill:

For each individual book, information on the number of books should be provided copies sold and their value for the current and previous year. The columns listed are number and amount. Information dissert at cost loss.

I've come up with this code:

SELECT title, SUM(buy_book.amount) AS Количество, SUM(price*buy_book.amount) AS Сумма
FROM buy_book
INNER JOIN book ON book.book_id = buy_book.book_id
INNER JOIN buy ON buy.buy_id = buy_book.buy_id
INNER JOIN buy_step ON buy_step.buy_id = buy.buy_id
WHERE buy_step.date_step_end IS NOT NULL
GROUP BY title
UNION ALL
SELECT title, COUNT(buy_archive.amount) AS Количество, SUM(buy_archive.price*buy_archive.amount) AS Сумма
FROM buy_archive
INNER JOIN book ON book.book_id = buy_archive.book_id
GROUP BY title
ORDER BY Сумма DESC;

Of course he doesn't work completely. He doesn't group the sales of this year and the past, the ones in buy_archive. I need a request, but how to correctly build his ears is missing. Tell me who's willing.

[yajahira]

SELECT title, SUM(Количество) AS Количество, SUM(Сумма) AS Сумма

From (SELECT book.title, SUM(buy_book.amount) AS Quantity,

SUM(book.price*buy_book.amount) AS AS Amount

 FROM buy_book
 INNER JOIN book USING (book_id)

 INNER JOIN buy USING(buy_id)

 INNER JOIN buy_step USING(buy_id)

 INNER JOIN step USING(step_id)

WHERE name_step ='Оплата' AND date_step_end IS NOT NULL

GROUP BY book.title

UNION ALL

SELECT book.title, SUM(buy_archive.amount) AS Quantity,

SUM(buy_archive.price*buy_archive.amount) AS AS Amount

    FROM buy_archive

    INNER JOIN book USING (book_id)

GROUP BY book.title) AS qwert

GROUP BY title

ORDER BY DESC