如何将此sql查询更改为派生表?

Question

这是原始查询

SELECT
YEAR(o.order_date) AS year,
COUNT(i.order_id) AS total
FROM 
sales.order_items AS i
INNER JOIN 
sales.orders AS o ON i.order_id = o.order_id
GROUP BY 
YEAR(o.order_date)

这是我到目前为止所做的

SELECT
YEAR(time) AS year,
SUM(price) AS total
FROM 
(SELECT
YEAR (o.order_date) AS time,
SUM (i.quantity * i.list_price) AS price
FROM 
sales.order_items AS i
INNER JOIN 
sales.orders AS o ON i.order_id = o.order_id) AS derived
GROUP BY 
YEAR(time)
ORDER BY 
YEAR(time)

我得到这个错误:

sales.orders列"。order_date'在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句

中

Answer 1

试试这个:

SELECT
year,
SUM(price) AS total
FROM (
SELECT
YEAR (o.order_date) AS year,
SUM (i.quantity * i.list_price) AS price
FROM sales.order_items AS i
JOIN sales.orders AS o
ON i.order_id = o.order_id
GROUP BY YEAR (o.order_date) 
) AS derived
GROUP BY year

但我认为这给出了相同的结果:

SELECT
YEAR (o.order_date) AS year,
SUM (i.quantity * i.list_price) AS price
FROM sales.order_items AS i
JOIN sales.orders AS o
ON i.order_id = o.order_id
GROUP BY YEAR (o.order_date)