CODEWITHSUNDEEP
sqlgroup-bysql-server-2016
coblenski
coblenski

Reputation: 1189

SQL GROUP BY alias

I have a smallint column which is used to represent a time. I've written the below code to derive a result depending on what the time is:

        CASE
    WHEN 
        CONVERT(varchar, t0.U_ORC_BE_ProdTime, 108) BETWEEN '07:00:00' AND '19:00:00' THEN '1'
    ELSE '2'
END  AS [Shift],
    --If time is between 7am and 7pm then Shift 1, else Shift 2

I want to insert it into a query I already have and then have it also group by the shift.

    SELECT 

T6.U_ORC_BE_StyleName AS [Style Name],
T0.[ItemCode],
STUFF(T0.[ItemCode], 1, 6, '') AS [Shortened ItemCode],
SUM(T0.[PlannedQty]) AS [Planned Qty],
SUM(T0.CmpltQty) AS [Actual Qty], 
SUM(T0.CmpltQty) - SUM(T0.PlannedQty) AS [Qty Difference],
SUM(T5.U_ORC_BE_HECTOLITER * T0.CmpltQty) AS [Total Hectoliters],

CASE
    WHEN [.......]
    THEN
    WHEN [.......]
    THEN
END AS [Line],

T0.U_ORC_BE_ProdDate AS [Date Produced]

    FROM [.......]

    GROUP BY T6.U_ORC_BE_StyleName, T0.[ItemCode], T5.Code , T0.U_ORC_BE_ProdDate

It keeps throwing up an error though which I suspect is due to attempting to group by the alias name.

"Each GROUP BY expression must contain at least one column that is not an outer reference."

Upvotes: 0

Views: 1781

Answers (2)

cableload
cableload

Reputation: 4375

As described by the error, you cannot group by the alias name. You would have to replicate the entire case when after your group by.

 select 
 ....
 group by 
 T6.U_ORC_BE_StyleName, T0.[ItemCode], T5.Code , T0.U_ORC_BE_ProdDate,
 CASE
    WHEN [.......]
    THEN
    WHEN [.......]
    THEN
END

Upvotes: 1

morgb
morgb

Reputation: 2312

To group by a calculated column like a case statement, you have to include that statement in the group by also. For example:

SELECT
  CASE ... END AS CaseStatement,
  [YourColumns]
FROM YourTable
GROUP BY CASE ... END

Upvotes: 2

Related Questions