producttotals.sql

Mastering VBNet Include Source Code

SELECT   dbo.Products.ProductName,
         SUM(dbo.[Order Details].Quantity)
                  AS [Total Items],
         COUNT(DISTINCT dbo.Orders.OrderID)
                  AS [Times Ordered],
         SUM(dbo.[Order Details].Quantity *
             dbo.Products.UnitPrice) AS Revenue
FROM     dbo.Products
         INNER JOIN dbo.[Order Details] ON
               dbo.Products.ProductID =
                       dbo.[Order Details].ProductID
               INNER JOIN dbo.Orders ON
               dbo.[Order Details].OrderID =
                       dbo.Orders.OrderID
WHERE    (dbo.Orders.OrderDate > @FromDate) AND
         (dbo.Orders.OrderDate < @ToDate)
GROUP BY dbo.Products.ProductName
ORDER BY dbo.Products.ProductName