SQL Case Statement and Group By-CodePudding

I've been having issues with this formula. I am trying to make the data compact using case statement to group using duration into 15 mins, 30 mins, 1 hour, etc. However whenever I run below code. It does not do the aggregation based on Transaction Date, Area, Code, Duration Group.

SELECT
    [Area],
    CONVERT(VARCHAR(10),[Time],111) as [Transaction Date],
    [Code],
    CASE        
        WHEN [Duration] >= 7200 THEN '5 Days or More'
        WHEN [Duration] >= 5760 AND [Duration] < 7200 THEN '4 Days'
        WHEN [Duration] >= 4320 AND [Duration] < 5760 THEN '3 Days'
        WHEN [Duration] >= 2880 AND [Duration] < 4320 THEN '2 Days'
        WHEN [Duration] > 1380 AND [Duration] < 2880 THEN '1 Days'
        ELSE '0 Days' 
    END AS 'Duration Group',
    SUM ([#ofSales]) AS [Volume]
FROM 
    my Table
WHERE 
    [ID] NOT LIKE '8787%' 
    AND [#ofSales] >= 1
GROUP BY 
    [Time], [Area], [Code],
    CASE        
        WHEN [Duration] >= 7200 THEN '5 Days or More'
        WHEN [Duration] >= 5760 AND [Duration] < 7200 THEN '4 Days'
        WHEN [Duration] >= 4320 AND [Duration] < 5760 THEN '3 Days'
        WHEN [Duration] >= 2880 AND [Duration] < 4320 THEN '2 Days'
        WHEN [Duration] > 1380 AND [Duration] < 2880 THEN '1 Days'
        ELSE '0 Days' 
    END
ORDER BY 
    [Transaction Date], [Area], [Duration Group]

CodePudding user response：

You might get the error in the phrase "order by" Because of your use of the column But try the following, it may be simpler by using a sub query

SELECT [Transaction_Date], [Area], [Duration_Group], SUM ([#ofSales]) AS [Volume]from (
     SELECT
       [Area]
      ,CONVERT(VARCHAR(10),[Time],111) as [Transaction_Date]
      ,[Code]
      ,[#ofSales]
      ,CASE         
        WHEN [Duration] >= 7200 THEN '5 Days or More'
        WHEN [Duration] >= 5760 AND [Duration] < 7200 THEN '4 Days'
        WHEN [Duration] >= 4320 AND [Duration] < 5760 THEN '3 Days'
        WHEN [Duration] >= 2880 AND [Duration] < 4320 THEN '2 Days'
        WHEN [Duration] > 1380 AND [Duration] < 2880 THEN '1 Days'
        ELSE '0 Days' 
    END AS Duration_Group
  FROM my Table
  WHERE [ID] Not Like '8787%' AND [#ofSales] >= 1
 ) tt
Group BY [Transaction_Date], [Area], [Duration_Group]   
ORDER BY [Transaction_Date], [Area], [Duration_Group]

Do not use spaces in column names