GROUP BY表达式必须至少包含一列

I have changed my query from oracle to sql and done few changes and Now I have my final sql server query but I am getting an error"Each GROUP BY expression must contain at least one column that is not an outer reference" when I run the below query.`

我已经将我的查询从oracle更改为sql并进行了一些更改,现在我有了我的最终sql server查询但是我收到错误“每个GROUP BY表达式必须包含至少一个不是外部引用的列”当我运行时以下查询

SELECT
                    startdate AS EnforcementDate,
                    'NULL' AS DistrictNo,
                    CASE
                        WHEN LEN(COALESCE(civicno,0))<=2
                        THEN '0'
                        WHEN LEN(COALESCE(civicno,0))>2
                        THEN ISNULL(SUBSTRING(cast(civicno as VARCHAR(6)),1,LEN(civicno)-2), '')+'00'
                    END                     AS civicno,
                    UPPER(streetname)       AS streetname,
                    COUNT(*)                AS TimingCount,
                    0                       AS ticketcount,
                    0                       AS LPRCount,
                    0                       AS ROSACount,
                    STUFF(( SELECT ','+ AGENTNO FROM platetiming a Where 
                    b.AGENTNO = a.AGENTNO FOR XML PATH('')),1 ,1, '') agentno ,
                    0                                  AS PRVCount,
                    0                            AS OfficersCount 
                FROM
                    platetiming b
                WHERE
                    b.startdate = 2015-01-01 and b.streetname is not null
                GROUP BY
                    b.startdate,
                  'NULL',
                    CASE
                        WHEN LEN(COALESCE(civicno,0))<=2
                        THEN '0'
                        WHEN LEN(COALESCE(civicno,0))>2
                        THEN ISNULL(SUBSTRING(cast(civicno as VARCHAR(6)),1,LEN(civicno)-2), '')+'00'
                    END ,
                    UPPER(streetname) 

`

1 个解决方案

#1

For GROUP BY, every column in your SELECT list should be in your GROUP BY clause. You're missing quite a few. Also, it would be easier to make use of a CTE, then your GROUP BY only has to deal with column names, not functions.

对于GROUP BY,SELECT列表中的每一列都应该在GROUP BY子句中。你错过了很多。此外,使用CTE会更容易,然后您的GROUP BY只需要处理列名,而不是函数。

Here is a example of how I would write it, make changes as necessary:

以下是我将如何编写它的示例,根据需要进行更改:

with t1 as (
SELECT startdate AS EnforcementDate,
    'NULL' AS DistrictNo,
    CASE
    WHEN LEN(COALESCE(civicno,0))<=2
    THEN '0'
    WHEN LEN(COALESCE(civicno,0))>2
    THEN ISNULL(SUBSTRING(cast(civicno as VARCHAR(6)),1,LEN(civicno)-2), '')+'00'
    END  AS civicno,
    UPPER(streetname) AS streetname,
    COUNT(*) AS TimingCount,
    0 AS ticketcount,
    0 AS LPRCount,
    0 AS ROSACount,
    STUFF(( SELECT ','+ AGENTNO FROM platetiming a Where 
        b.AGENTNO = a.AGENTNO FOR XML PATH('')),1 ,1, '') agentno ,
    0 AS PRVCount,
    0 AS OfficersCount 
    FROM platetiming b
    WHERE b.startdate = 2015-01-01 and b.streetname is not null 
)
select *, count(*) as TimingCount
from t1
GROUP BY
startdate, DistrictNo, civicno, streetname, ticketcount, LPRCount, ROSACount, PRVCount, OfficersCount