SQL Query with Group By statement

debugcn Published at Dev

www1986

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

Jibin Balachandran

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

Collected from the Internet

Please contact [email protected] to delete if infringement.