加入两个一对多表

Question

我有这两个表，我必须写这个SQL查询（Microsoft Access中）之间：

• 撰写出了每个作者的销量任何书籍有多少头衔查询（book_id）和多少个单位（订购的数量），他们卖

我试图与该查询，但它不工作

SELECT authors.author_id, Count(orders.book_id) AS CountOfbook_id, 
Sum(orders.quantity_ordered) AS SumOfquantity_ordered 
FROM authors 
LEFT JOIN orders ON authors.book_id = orders.book_id 
GROUP BY authors.author_id; 

，但它不工作，因为它计算同一本书多次从customersOrders

Answer 1

这个怎么样了？

select author,sum(CountOfbook_id), sum(SumOfquantity_ordered) 
from (
    SELECT authors.author_id as author, Count(orders.book_id) AS CountOfbook_id, 
    Sum(orders.quantity_ordered) AS SumOfquantity_ordered 
    FROM authors 
    LEFT JOIN orders ON authors.book_id = orders.book_id 
    GROUP BY authors.author_id,authors.book_id;) 
group by author;