SQL Server Create ranges with CASE statemant - ERROR: Each GROUP BY expression must contain at least one column that is not an outer reference

nqwrtyyt 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(135)

I'm tring to create ranges using CASE but I have a problem with counting the numbers of appearances. At the moment my code looks like this:

SELECT
CASE
    WHEN engine_capacity < 1 THEN 'less than 1'
    WHEN engine_capacity >= 1 AND engine_capacity <= 2 THEN '1.0 - 2.0'
    WHEN engine_capacity >= 2 AND engine_capacity < 3 THEN '2.0 - 3.0'
    WHEN engine_capacity >= 3 AND engine_capacity < 4 THEN '3.0 - 4.0'
    WHEN engine_capacity >= 4 AND engine_capacity < 5 THEN '4.0 - 5.0'
    ELSE 'more than 5' 
    END AS 'Range',
    COUNT(1) AS 'Count'
FROM cars
GROUP BY 'Range';

and I have an error:

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

sql-server

来源：https://stackoverflow.com/questions/77142166/create-ranges-with-case-statemant-error-each-group-by-expression-must-contain

2条答案

按热度按时间

sqyvllje1#

You need to include the full CASE statement in the GROUP BY clause:

SELECT
CASE
    WHEN engine_capacity < 1 THEN 'less than 1'
    WHEN engine_capacity >= 1 AND engine_capacity <= 2 THEN '1.0 - 2.0'
    WHEN engine_capacity >= 2 AND engine_capacity < 3 THEN '2.0 - 3.0'
    WHEN engine_capacity >= 3 AND engine_capacity < 4 THEN '3.0 - 4.0'
    WHEN engine_capacity >= 4 AND engine_capacity < 5 THEN '4.0 - 5.0'
    ELSE 'more than 5' 
    END AS [Range],
    COUNT(1) AS [Count]
FROM cars
GROUP BY 
    CASE
        WHEN engine_capacity < 1 THEN 'less than 1'
        WHEN engine_capacity >= 1 AND engine_capacity <= 2 THEN '1.0 - 2.0'
        WHEN engine_capacity >= 2 AND engine_capacity < 3 THEN '2.0 - 3.0'
        WHEN engine_capacity >= 3 AND engine_capacity < 4 THEN '3.0 - 4.0'
        WHEN engine_capacity >= 4 AND engine_capacity < 5 THEN '4.0 - 5.0'
        ELSE 'more than 5' 
    END

Or, use a subquery before grouping:

SELECT 
    [Range], 
    count(*) as [Count]
from
(
    SELECT
    CASE
        WHEN engine_capacity < 1 THEN 'less than 1'
        WHEN engine_capacity >= 1 AND engine_capacity <= 2 THEN '1.0 - 2.0'
        WHEN engine_capacity >= 2 AND engine_capacity < 3 THEN '2.0 - 3.0'
        WHEN engine_capacity >= 3 AND engine_capacity < 4 THEN '3.0 - 4.0'
        WHEN engine_capacity >= 4 AND engine_capacity < 5 THEN '4.0 - 5.0'
        ELSE 'more than 5' 
        END AS [Range]
    FROM cars
) a
GROUP BY [Range]

赞(0）回复(0）举报 2023-10-15

vhipe2zx2#

SELECT
CASE
    WHEN engine_capacity < 1 THEN 'less than 1'
    WHEN engine_capacity >= 1 AND engine_capacity <= 2 THEN '1.0 - 2.0'
    WHEN engine_capacity >= 2 AND engine_capacity < 3 THEN '2.0 - 3.0'
    WHEN engine_capacity >= 3 AND engine_capacity < 4 THEN '3.0 - 4.0'
    WHEN engine_capacity >= 4 AND engine_capacity < 5 THEN '4.0 - 5.0'
    ELSE 'more than 5' 
    END AS 'Range',
    COUNT(1) AS 'Count'
FROM cars
GROUP BY CASE
    WHEN engine_capacity < 1 THEN 'less than 1'
    WHEN engine_capacity >= 1 AND engine_capacity <= 2 THEN '1.0 - 2.0'
    WHEN engine_capacity >= 2 AND engine_capacity < 3 THEN '2.0 - 3.0'
    WHEN engine_capacity >= 3 AND engine_capacity < 4 THEN '3.0 - 4.0'
    WHEN engine_capacity >= 4 AND engine_capacity < 5 THEN '4.0 - 5.0'
    ELSE 'more than 5' 
    END

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Create ranges with CASE statemant - ERROR: Each GROUP BY expression must contain at least one column that is not an outer reference

2条答案

相关问题

热门标签

最新问答