www1986 www1986 - 2 months ago 7
SQL Question

SQL Query with Group By statement

I have this 4 table:

Managers, Sales, SaleDetails, Products:


  • Managers: ID, Name

  • Sales: ID, ManagerID, SaleNO, SaleDate

  • SaleDetails: ID, SaleID, ProductID, Quantity

  • Products: ID, Name, Price



I want to retrive for each manager: SaleDate and unique Product count;

My select looks like this:

;WITH cte
AS
(
SELECT sd.SaleID, sd.ProductID FROM dbo.Products p
INNER JOIN dbo.SalesDetails sd ON sd.ProductID = p.ID
GROUP BY sd.SaleID, sd.ProductID
)
SELECT
c.Name AS ManagerName
,s.SaleDate
,COUNT(ct.ProductID) AS ProductCount
FROM cte ct
INNER JOIN dbo.Sales s ON ct.SaleID = s.ID
INNER JOIN dbo.Managers c ON c.ID = s.ConsultantID
GROUP BY s.SaleDate, c.Name


Is this optimal? Can you help my to replace it with more optimal query


  • Managers: [ID = 1, Name = John;]

  • Sales: [ID = 1, ManagerID = 1, SaleNO = 0015, SaleDate: 2016-09-08], [ID = 2, ManagerID = 1, SaleNO = 0016, SaleDate: 2016-09-09]

  • SaleDetails: [ID = 1, SaleID = 1, ProductID = 1, Quantity = 2], [ID =
    2, SaleID = 1, ProductID = 1, Quantity = 4], [ID = 3, SaleID = 1,
    ProductID = 2, Quantity = 3], [ID = 4, SaleID = 2,
    ProductID = 1, Quantity = 3]

  • Product: [ID = 1, Name = Sony], [ID = 2, Name = Samsung]



Query must return results:


  • ManagerName = John, SaleDate = 2016-09-08, ProductCount = 2

  • ManagerName = John, SaleDate = 2016-09-09, ProductCount = 1


Answer
SELECT c.Name AS ManagerName
      ,s.SaleDate
      ,COUNT(p.ProductID)
FROM dbo.Products p
INNER JOIN dbo.SalesDetails sd ON sd.ProductID = p.ID
INNER JOIN dbo.Sales s ON sd.SaleID = s.ID
INNER JOIN dbo.Managers c ON c.ID = s.ConsultantID
GROUP BY s.SaleDate, c.Name,sd.ProductID