Single Item Order – AdventureWorks

I am wondering if my answer works for this problem? The answer I was able to find on the internet seems similar but they join another table which seems ambiguous.

Question: A “Single Item Order” is a customer order where only one item is ordered. Show the SalesOrderID and the UnitPrice for every Single Item Order.

Their answer:

select sod.SalesOrderID, sod.UnitPrice
from SalesLT.SalesOrderDetail sod
inner join SalesLT.SalesOrderHeader soh on soh.SalesOrderID =
sod.SalesOrderID
where sod.SalesOrderID in (
                          select s.SalesOrderID as ordernums
                          from SalesLT.SalesOrderDetail s
                          group by s.SalesOrderID
                          having COUNT(*) = 1
                          );

My Answer:

SELECT SalesOrderID, UnitPrice
FROM SalesOrderDetail
GROUP BY SalesOrderID
HAVING COUNT(SalesOrderID) = 1;

Answer

If you run your answer, you’ll get an error because UnitPrice is not in the GROUP BY. If you add it, your HAVING will not work.

The solution is simple . . . you need an aggregation function:

SELECT SalesOrderID, MAX(UnitPrice) as UnitPrice
FROM SalesOrderDetail
GROUP BY SalesOrderID
HAVING COUNT(SalesOrderID) = 1;