I'm currently trying to solve an issue revolving around summarizing a list of publishers, their total revenue, total payouts, and their net profit. What makes this slightly complicated is that the total payout is contingent on a case statement (due to having to choose between the higher value of royalties). This case statement was perfectly fine and executed in a previous query that you can see on the SQLFiddle link down below. My issue is that I have a near finished query that addresses what I need but I don't know what correction to make for it to complete. Help would be super appreciated! And if you get it to work, you would be a legit lifesaver!!
Select name,
SUM(book.msrp) AS 'Total Revenue',
SUM(EarningByBook) AS 'Total Payouts',
SUM(book.msrp)-SUM(EarningByBook) AS 'Net Profit'
FROM
(SELECT publisher.name, book.msrp,
(SELECT
CASE WHEN preferred_royalties > standard_royalties
THEN preferred_royalties*copies_sold
ELSE standard_royalties*copies_sold END
AS 'EarningByBook',
copies_sold ,
YEAR(CURDATE())-YEAR(date_published) Years
INNER JOIN book ON publisher.id = book.publisher_id)
FROM author A
JOIN book B ON A.id=B.author_id
JOIN publisher P ON B.publisher_id=P.id)
From publisher
INNER JOIN book ON publisher.id = book.publisher_id) Z
GROUP BY
name;
The SQL fiddle is as follows : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b0015a0a4286f9b2c064bbd65525faa5&hide=13312
The output expected should look
| Publisher | Total Revenue | Total Payouts | Net Profit |
|---|---|---|---|
| name | 20000 | 1500 | 18500 |
| name | 15000 | 1000 | 14000 |
Consider flattening all your inner selects to a single SELECT subquery.
SELECT sub.publisher
, SUM(sub.msrp) AS 'Total Revenue'
, SUM(sub.EarningByBook) AS 'Total Payouts'
, SUM(sub.msrp) - SUM(sub.EarningByBook) AS 'Net Profit'
FROM
(SELECT
P.`name` AS publisher
, CASE
WHEN A.preferred_royalties > P.standard_royalties
THEN A.preferred_royalties * B.copies_sold
ELSE P.standard_royalties * B.copies_sold
END AS EarningByBook
, YEAR(CURDATE()) - YEAR(B.date_published) AS Years
, B.msrp
, B.copies_sold
FROM author A
INNER JOIN book B
ON A.id = B.author_id
INNER JOIN publisher P
ON B.publisher_id = P.id
) AS sub
GROUP BY
sub.publisher;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments