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



  • 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.



  • 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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2