Join table to CTE and group by-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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