SQL Help with "invalid in the select list because it is not contained in either an aggregate function..."

Hi,

changed the line:

sum(ProjGeogEnrollment)  as myCount


to

sum(z.ProjGeogEnrollment)  as myCount

You see, the #myLoadSeq table contains something like this:


select ProjGeogEnrollment , OrgHNoCategory, StCtyCode
from #myLoadSeq

projGeogEnrollment     OrgHNoCategory                                                    StCtyCode
74                                 SomeDataA                                                            05470
365                               SomeDataB                                                            05470
4060                             SomeDataB                                                           05470
576                               SomeDataB                                                           05470
3                                   SomeDataB                                                           05470

The end result hopefully should look something like the following


16.50      0      0.00      74      NULL      0      0.00   <---- SomedataA
16.50      1      100.00      5004      87.12      1      100.00  <----SomeDataB

do you still get that error?

no, I am not getting the error.  Just that the sum(ProjGeogEnrollment) syntax is not producing the correct results.

I think I got it now.  The solution is as follows:

SELECT
                        @currentCutoff AS CurrentCutoff,
                        COUNT(DISTINCT il1.H_Number) AS ViolatingContracts,
                        (CONVERT(DECIMAL, COUNT(DISTINCT il1.H_Number)) / CONVERT(DECIMAL, @TotalContracts)) * 100 AS PercentViolatingContracts,
                        --SUM(il1.ProjGeogEnrollment) AS AffectedEnrollment,
                        SUM(DISTINCT z.myCount ) AS AffectedEnrollment,
                        (CONVERT(DECIMAL, SUM(il1.ProjGeogEnrollment)) / CONVERT(DECIMAL, @TotalProjGeogEnrollment)) * 100 AS PercentAffectedEnrollment,
                        COUNT(DISTINCT il1.Parent_Org) AS ViolatingParentOrgs,
                        (CONVERT(DECIMAL, COUNT(DISTINCT il1.Parent_Org)) / CONVERT(DECIMAL, @TotalParentOrgs)) * 100 AS PercentViolatingParentOrgs
                  FROM
                        #myLoadSeq il1
                        LEFT JOIN #myLoadSeq il2 ON  il1.OrgHNoCategory = il2.OrgHNoCategory
                              AND il1.StCtyCode = il2.StCtyCode
                              AND il1.Seq = il2.Seq + 1
                        LEFT JOIN #myLoadSeq il3 ON il1.OrgHNoCategory = il3.OrgHNoCategory
                              AND il1.StCtyCode = il3.StCtyCode
                              AND il1.Seq = il3.Seq - 1
                        INNER JOIN
                        (
                              SELECT ProjGeogEnrollment AS myCount, OrgHNoCategory, StCtyCode
                              FROM #myLoadSeq
                        ) AS z ON z.OrgHNoCategory = il1.OrgHNoCategory
                              AND z.StCtyCode = il1.StCtyCode
                  WHERE
                        il1.OOPC_Grand_Total - il2.OOPC_Grand_Total < @currentCutoff
                        OR il3.OOPC_Grand_Total - il1.OOPC_Grand_Total < @currentCutoff

I've requested that this question be closed as follows:

Accepted answer: 0 points for yechan's comment #37862739

for the following reason:

bypassing the group by error by adding an inner join and then sum it up in the select statement

that is not right
i got you in the right direction and got you over that syntax error
since i don't have your data i can't check if the results are correct