sql join query through Adventure works database I want to show a list of Product Colors. For each Color then show how many SalesDetails there are and the Total SalesAmount (UnitPrice * OrderQty). Only show Colors with a Total SalesAmount more than $50,000 and eliminate the products that do not have
a color.

Please see my query below:

select * from Production.Product
select * from Sales.SalesOrderDetail

Select pr.Color
from Production.Product as pr
left Join 
--on pr.ProductID=sr.ProductID
(SELECT  count( sr.SalesOrderDetailID) as count1,sum(sr.UnitPrice * sr.OrderQty) as total
     FROM    Sales.SalesOrderDetail as sr
     GROUP BY sr.ProductID,
     HAVING  sum(sr.UnitPrice * sr.OrderQty) &gt;50000
    ) as sa
 on sa.ProductID=pr.ProductID


I am not sure what to do after this point.please help
Select pr.Color, count(*), sum(sr.UnitPrice * sr.OrderQty) as total
from Production.Product as pr
Join Sales.SalesOrderDetail as sr
on sr.ProductID=pr.ProductID
WHERE pr.Color IS NOT NULL
GROUP BY pr.Color
HAVING  sum(sr.UnitPrice * sr.OrderQty) &gt;50000
 ;