将表与CTE和CTE和Group一起

发布于 2025-02-07 08:52:34 字数 368 浏览 1 评论 0原文

我有一个包含大量连接和状况（mydashboard）的CTE和我试图将CTE加入另一个表格，并显示一个带有第二个表的计数的额外列。

我在做什么错？

select *, count(t_KPIRespConn.RespID)
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid
group by mydashboard.KPIcodeID

列“ mydashboard.code”在选择列表中无效，因为它不包含在汇总函数中，或者由子句中的组中包含。

谢谢

原文

I have a CTE that contains lots of joins and condition (mydashboard) and
I am trying to join the CTE to another table and show an additional column with the count of the second table.

What am I doing wrong?

select *, count(t_KPIRespConn.RespID)
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid
group by mydashboard.KPIcodeID

Column 'mydashboard.code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks

分享到QQ

分享到微博

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

发布评论

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

花间憩 2025-02-14 08:52:34

您将需要按所有非汇总字段进行分组，这意味着选择列表中未在聚合函数中使用的所有字段，在您的情况下，除了t_kpirespconn.respid外，所有字段。

解决方案1：

select field1, field2, field3,... fieldN, count(t_KPIRespConn.RespID)
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid
group by mydashboard.KPIcodeID, field1, field2, field3, ...fieldN

使用窗口函数基本上实现了同一件事，但详细的内容较少。当窗口函数汇总了指定的分区上的值时，您不需要组。对于count（）您可以通过（）指定，这意味着组中设置的整个结果。

解决方案2：

select *, 
count(*) OVER() //<-- Use this if you want the count of all records
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid

使用相同的窗口功能，但将分区范围缩小到您想要的任何分组。在这种情况下，计数将与匹配t_kpirespconn.respid值的所有记录有关。

解决方案3：

 select *, count(*) OVER(PARTITION BY t_KPIRespConn.RespID) //<-- Use this if you want the count of all records with the same t_KPIRespConn.RespID
 from mydashboard
 join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid

You will need to group by all non aggregated fields, meaning all fields in the SELECT list that are not used in an aggregate function, in your case, all field besides t_KPIRespConn.RespID.

Solution 1:

select field1, field2, field3,... fieldN, count(t_KPIRespConn.RespID)
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid
group by mydashboard.KPIcodeID, field1, field2, field3, ...fieldN

Using a window function basically achieves the same thing but it is less verbose. You are not required to GROUP BY as the window function aggregates the values on the partition specified. For COUNT() you can specify OVER(), which means the entire results set in the group.

Solution 2:

select *, 
count(*) OVER() //<-- Use this if you want the count of all records
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid

Use the same window function but narrow the partition to any grouping you would like. In this case, the counts would pertain to all records with matching t_KPIRespConn.RespID values.

Solution 3:

 select *, count(*) OVER(PARTITION BY t_KPIRespConn.RespID) //<-- Use this if you want the count of all records with the same t_KPIRespConn.RespID
 from mydashboard
 join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid

回复收藏 0 原文

合约呢 2025-02-14 08:52:34

选择 * ..组成的MyDashboard.kpicodeid是您的真实问题。运行汇总功能时，必须明确提供组中选择列表中未被聚集的任何列。这将起作用

select mydashboard.KPIcodeID, count(t_KPIRespConn.RespID)
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid
group by mydashboard.KPIcodeID

select * .. group by mydashboard.KPIcodeID is your true issue. When you run aggregate functions, you must explicitly supply any columns that are non-aggregated in the select list in the group by. This will work

select mydashboard.KPIcodeID, count(t_KPIRespConn.RespID)
from mydashboard
join t_kpirespconn on mydashboard.kpicodeid = t_kpirespconn.kpicodeid
group by mydashboard.KPIcodeID

回复收藏 0 原文

~没有更多了~