1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SELECT *
FROM ( SELECT pp.ProductID,
TRIM(CONCAT(pp.[Name], ' ', '(', pp.ProductNumber, ')')) as DescProd,
pp.Color as Couleur,
COALESCE(d.Title, 'Pas de documentation disponible') as Titre,
COUNT(sod.OrderQty) over (partition by pp.ProductID) as NbreVendu,
FORMAT(AVG(sod.LineTotal) over (partition by pp.ProductID), 'c', 'en-us') as MoyVente
FROM Production.Product as pp
LEFT JOIN Production.ProductDocument as ppd
ON ppd.ProductID = pp.ProductID
LEFT JOIN Production.Document as d
ON d.DocumentNode = ppd.DocumentNode
INNER JOIN Sales.SalesOrderDetail as sod
ON sod.ProductID = pp.ProductID
) as x
GROUP BY x.ProductID
HAVING x.NbreVendu > 10
ORDER BY x.ProductID; |
Partager