SQL Query - Syntax Understanding

Question

I want to show the best-selling product by quantity

Product Table
ProductID  ProductName
1          AA   
2          BB
3          CC
[Order Details] Table
OrderID ProductID  Quantity DateOfOrder
1       1            10    SomeDate   
2       1            100     ,,
3       2            15      ,, 
4       1            15      ,,   
5       2            20      ,, 
6       2            30      ,, 
7       1            100     ,,

Expected Output

Product By Quantity  AA

Since the amount (amount) = 225

I used:

select 'Product By Quantity' + ProductName 
from
Products 
where ProductID in
 (select 
       ProductID
  from 
       [Order Details] det 
  where Quantity=
                (
                  select max(SUM(Quantity)) 
                  from [Order Details] od
                  where
                  od.ProductID=det.ProductID
                )
  )  

I got an error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

Please explain to me why the syntax fails here, so in the future I will write the corresponding query knowing the correct syntax. Also give me the correct request.

Thanks to everyone in advance.

Edit

I tried to execute the following query

SELECT 'Best Selling Product'+ProductName
FROM 
Products
WHERE ProductID =
 (
       SELECT ProductID
       FROM [Order Details]
       GROUP BY ProductID
       HAVING SUM(Quantity) = (
                               SELECT MAX(SQ)
                               FROM (
                                       SELECT SUM(Quantity) as SQ
                                       FROM [Order Details]
                                       GROUP BY ProductID
                                    ) AS OD))

Answer 1

Try the following:

select 'Product By Quantity' + ProductName  from Products p
join 
(
select top 1 sum(Quantity) sq, od.ProductId 
from [Order Details] od
group by od.ProductId 
order by 1 desc
) bsp on p.productid = bsp.ProductId

bsp means best selling product

Answer 2

I think this is what you are trying to get to:

select top 1 p.product_name, sum(od.quantity) as total_quantity
    from products p
        inner join [order details] od
            on p.productid = od.productid
    group by p.productid, p.product_name
    order by total_quantity desc

Answer 3

select max (SUM ()) . . ()?

Answer 4

:

SELECT      TOP 1
            SUM(o.Quantity)
            ,p.ProductName
FROM        [Order Details] AS o
INNER JOIN  [Products] AS p ON p.ProductID = o.ProductID
GROUP BY    p.ProductID
            ,p.ProductName
ORDER BY    SUM(o.Quantity) DESC