How can I use a new created column in GROUP BY-CodePudding

In my SQL query, I want to check if a column has the string 'test' and safe the value in a new column

When I use the new column in GROUP BY, I get error saying it can't find the new column:

SELECT
    CAST([EvtTime] as date) AS myDay,
    CASE WHEN [result] LIKE '%test%' THEN 1 ELSE 0 END AS testResult,
    COUNT_BIG(*) AS myCount,
    [name]
FROM [mytable]
GROUP BY myDay, testResult

I am using SQL Server.

CodePudding user response：

CROSS APPLY is a clean way to solve this issue.

SELECT
    CAST([EvtTime] as date) AS myDay,
    X.testResult,
    COUNT_BIG(*) AS myCount,
    [name]
FROM myTable
CROSS APPLY (VALUES (CASE WHEN [result] LIKE '%test%' THEN 1 ELSE 0 END)) AS X(testResult)
GROUP BY myDay, testResult

CodePudding user response：

You can use inline view:

SELECT *,
        COUNT_BIG(*) AS myCount
FROM (
    SELECT
        CAST([EvtTime] as date) AS myDay,
        CASE WHEN [result] LIKE '%test%' THEN 1 ELSE 0 END AS testResult,
        [name]
    FROM [dbo].[aria_auth_operations]
) MyInlineView
GROUP BY myDay, testResult