This is the code I'm trying to run

select min(sod.ModifiedDate) as [ModifiedDate]
    ,Bikes = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Bikes' and ModifiedDate = sod.ModifiedDatee)
    ,Components = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Components' and ModifiedDate = sod.ModifiedDate)
    ,Clothing = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Clothing' and ModifiedDate = sod.ModifiedDate )
    ,Accessories = (select sum(LineTotal) from SalesOrderDetail where max(ProductCategoryName) = 'Accessories' and ModifiedDate = sod.ModifiedDate )

from SalesOrderDetail sod
inner join product p on p.ProductID = sod.ProductID
inner join ProductSubcategory ps on ps.ProductSubcategoryID = p.ProductSubcategoryID
inner join ProductCategory pc on pc.ProductCategoryID = ps.ProductCategoryID

group by ProductCategoryName
    ,sod.ModifiedDate
    ,datepart(year, sod.ModifiedDate) 
    ,datepart(month, sod.ModifiedDate) 
    ,datepart(day, sod.ModifiedDate)        

order by datepart(year, sod.ModifiedDate) 
    ,datepart(month, sod.ModifiedDate) 
    ,datepart(day, sod.ModifiedDate)

I can't figure out how to make it so it splits the LineTotal into the four ProductNameCategory like this: (expected result)

All I get is this, it adds all the lineTotal for a given date regardless of ProductCategoryName and then puts the sum in Components, except when the only thing there is that day is Bikes, then he puts it in Bikes.

How can I make it look like the expected result without Pivot and case when? I need to get the results showed here using four different methods to then test performance and I already used pivot and case when. I'm trying to use this method I found

CodePudding user response：

Here's one way to do it without PIVOT or CASE, but it's really ugly as a result, even after pulling some of the joins and aggregations out of the main query:

WITH ProductMeta AS 
(
  SELECT p.ProductID, CatName = pc.Name
  FROM Production.Product AS p
  INNER JOIN Production.ProductSubcategory ps 
    on ps.ProductSubcategoryID = p.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc 
    on pc.ProductCategoryID = ps.ProductCategoryID
  WHERE pc.Name IN (N'Bikes',N'Components',N'Clothing',N'Accessories')
), Agg AS
(
  SELECT date = CONVERT(date, sod.ModifiedDate), 
    pm.CatName, 
    LineTotal = SUM(LineTotal)
  FROM Sales.SalesOrderDetail sod
  INNER JOIN ProductMeta AS pm ON sod.ProductID = pm.ProductID
  GROUP BY CONVERT(date, sod.ModifiedDate), pm.CatName
)
SELECT a.date
    ,Bikes       = (SELECT SUM(LineTotal) FROM Agg 
                    WHERE date = a.date AND CatName = 'Bikes')
    ,Components  = (SELECT SUM(LineTotal) FROM Agg 
                    WHERE date = a.date AND CatName = 'Components')
    ,Clothing    = (SELECT SUM(LineTotal) FROM Agg 
                    WHERE date = a.date AND CatName = 'Clothing')
    ,Accessories = (SELECT SUM(LineTotal) FROM Agg 
                    WHERE date = a.date AND CatName = 'Accessories')
FROM Agg AS a
GROUP BY a.date;