sql - Having issues summarizing years as decades in MySQL

Question

I was wondering if someone could spot the reason why my query is returning an error at the very end. The situation I am trying to solve is

For each decade since 1990 (1990, 2000, 2010), list the average MRSP of all books published in that decade, along with a count of copies sold and total sales $.

I was able to figure out the decades part with the following code

SELECT *,
CASE WHEN date_published BETWEEN '1990-01-01' AND '1999-12-31' THEN '1990'
WHEN date_published BETWEEN '2000-01-01' AND '2009-12-31' THEN '2000'
WHEN date_published BETWEEN '2010-01-01' AND '2019-12-31' THEN '2010'
ELSE 'Void'
END AS 'Decade', copies_sold
FROM book

However when I try to bring everything together in

SELECT 'Decade',
SUM(copies_sold) AS 'Copies Sold',
SUM(msrp*copies_sold) AS 'Total Sales',
SUM(msrp/copies_sold) AS 'Avg MSRP'

FROM
(SELECT *,
CASE WHEN date_published BETWEEN '1990-01-01' AND '1999-12-31' THEN '1990'
WHEN date_published BETWEEN '2000-01-01' AND '2009-12-31' THEN '2000'
WHEN date_published BETWEEN '2010-01-01' AND '2019-12-31' THEN '2010'
ELSE 'Void'
END AS 'Decade', copies_sold
FROM book) 

FROM book

I get the following error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM book' at line 15"

Can anyone help me close this out? It would be super appreciated! The current fiddle is

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5464a4f1103bd4d6ed98e504be5ed7fe

The final output should look something like

Decade | Copies Sold | Total Sales| Avg MSRP
1990 5 10000 58

question from:https://stackoverflow.com/questions/65915114/having-issues-summarizing-years-as-decades-in-mysql

Answer 1

You should exclude dates earlier than 1990 with a WHERE condition; there's no need to bother with them.

You can calculate decade more easily with just 10*FLOOR(YEAR(date_published)/10).

There's no need for a subquery, and you do need to group by your decade.

SELECT
    10*FLOOR(YEAR(date_published)/10) AS Decade,
    SUM(copies_sold) AS 'Copies Sold',
    SUM(msrp*copies_sold) AS 'Total Sales',
    SUM(msrp/copies_sold) AS 'Avg MSRP'
FROM book
WHERE date_published > '1989-12-31'
GROUP BY 1