An invalid statement is raised with SQL Server when using sum operation and order by with alias-CodePudding

While running the following query:

    SELECT
    (
        CASE
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 0 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 1000 ) ) THEN
                '58'
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 1001 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 10000 ) ) THEN
                '59'
            ELSE
                'Other'
        END
    ) AS cc_1658532399112,
    SUM(CAST(((JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"'))) AS FLOAT)) AS "group_by_1658532512558_0"
FROM
    acc.pod.mnnorway_tup_vw multi_pod
ORDER BY
    (
        CASE
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 0 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 1000 ) ) THEN
                0
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 1001 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 10000 ) ) THEN
                1
            ELSE
                2
        END
    ) ASC;

I am getting the following exception:

Msg 8120, Level 16, State 1, Line 4 Column 'acc.pod.mnnorway_tup_vw.struc_ppod' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I am removing the SUM statement, the query executes fine.

CodePudding user response：

Using SUM without a GROUP BY implies that you are grouping by the empty set ().

SQL disallows you from selecting or ordering in a grouped query anything other than a grouping value or an aggregated value.

It seems that the value that you are selecting is what you actually want to group by. So you need to add a GROUP BY clause. To prevent lots of repetition, you can use CROSS APPLY (VALUES to add calculated columns to the query. They can even refer one to the next.

SELECT
    v2.cc_1658532399112,
    SUM(v1.bbred) AS group_by_1658532512558_0

FROM
    acc.pod.mnnorway_tup_vw multi_pod

CROSS APPLY (VALUES (
    CONVERT(float, JSON_VALUE(CONVERT(nvarchar(max), multi_pod.struc_ppod), '$.bbred'))
) ) v1(bbred)

CROSS APPLY (VALUES (
    CASE
    WHEN v1.bbred >= 0 AND v1.bbred <= 1000
      THEN '58'
    WHEN v1.bbred >= 1001 AND v1.bbred <= 10000
      THEN '59'
    ELSE   'Other'
    END
) ) v2(cc_1658532399112)

GROUP BY
    v2.cc_1658532399112
ORDER BY
    v2.cc_1658532399112;

Side note: values between 1000 and 1001 fall through the cracks. Perhaps you want WHEN v1.bbred > 1000 AND v1.bbred <= 10000 THEN '59' instead.

CodePudding user response：

Replacing the integer value 0, 1 and 2 from the query with '58', '59' and 'Other' has resolved my issue.

SELECT

    (
        CASE
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 0 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 1000 ) ) THEN
                '58'
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 1001 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 10000 ) ) THEN
                '59'
            ELSE
                'Other'
        END
    ) AS cc_1658532399112,
    SUM(CAST(((JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"'))) AS FLOAT)) AS "group_by_1658532512558_0"
FROM
    acc.pod.mnnorway_tup_vw multi_pod
GROUP BY (
    CASE
        WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 0 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 1000 ) ) THEN
                '58'
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 1001 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 10000 ) ) THEN
                '59'
        ELSE
            'Other'
    END
)
ORDER BY
    (
    CASE
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 0 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 1000 ) ) THEN
                '58'
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 1001 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) <= 10000 ) ) THEN
                '59'
            ELSE
                'Other'
    END
) ASC;