mysql - How can I correct this query that involves a CASE statement for a summary?

Question

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

question from:https://stackoverflow.com/questions/65923040/how-can-i-correct-this-query-that-involves-a-case-statement-for-a-summary

Answer 1

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;