组由“外部参考”组成SQL Server中的错误

发布于 2025-02-09 03:46:23 字数 745 浏览 2 评论 0原文

我在下面的代码中使用一个简单的小组：

SELECT 'bcg-measles1'  AS vaccgroup,
        e0.providerid,
        ( SELECT count(DISTINCT e1.baseentityid) AS count
               FROM bcgmeasles1 e1
              WHERE e1.measles1_ = 'Vaccinated'  AND e0.providerid = e1.providerid) AS numerator,
        ( SELECT count(DISTINCT e2.baseentityid) AS count
               FROM bcgmeasles1 e2
              WHERE e2.measles1_ <> 'Not Eligible'  AND e0.providerid = e2.providerid) AS denominator
       FROM bcgmeasles1 e0
      GROUP BY 'bcg-measles1' , e0.providerid

我面临的错误说：

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

我不明白为什么出现此错误，因为我只是在做一个简单的组。请帮忙！

原文

I am using a simple group by statement in the code below:

SELECT 'bcg-measles1'  AS vaccgroup,
        e0.providerid,
        ( SELECT count(DISTINCT e1.baseentityid) AS count
               FROM bcgmeasles1 e1
              WHERE e1.measles1_ = 'Vaccinated'  AND e0.providerid = e1.providerid) AS numerator,
        ( SELECT count(DISTINCT e2.baseentityid) AS count
               FROM bcgmeasles1 e2
              WHERE e2.measles1_ <> 'Not Eligible'  AND e0.providerid = e2.providerid) AS denominator
       FROM bcgmeasles1 e0
      GROUP BY 'bcg-measles1' , e0.providerid

I am facing an error which says:

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

I do not understand why this error is showing up, as I am only doing a simple group by. Please help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉世弥音 2025-02-16 03:46:23

错误消息是

每组表达式必须至少包含一列
外部参考。

您有

GROUP BY 'bcg-measles1' , e0.providerid

'bcg-measles1'，这只是一个恒定的字符串字面。

删除该问题以解决问题（如@Charlieface所述）。

但是无论如何，产生这些结果的一种更简单的方法是使用以下

SELECT 'bcg-measles1'  AS vaccgroup,
       providerid,
       COUNT(DISTINCT CASE WHEN measles1_ = 'Vaccinated' THEN baseentityid END) AS numerator,
       COUNT(DISTINCT CASE WHEN measles1_ <> 'Not Eligible' THEN baseentityid END) AS denominator
FROM   bcgmeasles1
GROUP  BY providerid

The error message is

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

You have

GROUP BY 'bcg-measles1' , e0.providerid

This contains an expression 'bcg-measles1' that is just a constant string literal.

Remove that to resolve the issue (as noted by @Charlieface).

But in any event a simpler way of producing these results would be to use the below

SELECT 'bcg-measles1'  AS vaccgroup,
       providerid,
       COUNT(DISTINCT CASE WHEN measles1_ = 'Vaccinated' THEN baseentityid END) AS numerator,
       COUNT(DISTINCT CASE WHEN measles1_ <> 'Not Eligible' THEN baseentityid END) AS denominator
FROM   bcgmeasles1
GROUP  BY providerid

回复收藏 0 原文

~没有更多了~