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

yechan
yechan used Ask the Experts™
on
Hi,

when attempting to run the query below, I am getting the following error msg.  Not sure how to resolve it.

Msg 8120, Level 16, State 1, Line 14
Column '#myLoadSeq.OrgHNoCategory' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 15
Column '#myLoadSeq.StCtyCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT
      16.5 AS CurrentCutoff,
      COUNT(DISTINCT il1.H_Number) AS ViolatingContracts,
      (CONVERT(DECIMAL, COUNT(DISTINCT il1.H_Number)) / CONVERT(DECIMAL, 100)) * 100 AS PercentViolatingContracts,
      (CONVERT(DECIMAL, SUM(il1.ProjGeogEnrollment)) / CONVERT(DECIMAL, 100)) * 100 AS PercentAffectedEnrollment,
      COUNT(DISTINCT il1.Parent_Org) AS ViolatingParentOrgs,
      (CONVERT(DECIMAL, COUNT(DISTINCT il1.Parent_Org)) / CONVERT(DECIMAL, 100)) * 100 AS PercentViolatingParentOrgs,
      (
            select sum(ProjGeogEnrollment)
            from #myLoadSeq as z
            where z.OrgHNoCategory = il1.OrgHNoCategory
                  and z.StCtyCode = il1.StCtyCode
      ) as myCount
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
WHERE
      il1.OOPC_Grand_Total - il2.OOPC_Grand_Total < 16.5
      OR il3.OOPC_Grand_Total - il1.OOPC_Grand_Total < 16.5
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try
SELECT
      16.5 AS CurrentCutoff,
      COUNT(DISTINCT il1.H_Number) AS ViolatingContracts,
      (CONVERT(DECIMAL, COUNT(DISTINCT il1.H_Number)) / CONVERT(DECIMAL, 100)) * 100 AS PercentViolatingContracts,
      (CONVERT(DECIMAL, SUM(il1.ProjGeogEnrollment)) / CONVERT(DECIMAL, 100)) * 100 AS PercentAffectedEnrollment,
      COUNT(DISTINCT il1.Parent_Org) AS ViolatingParentOrgs,
      (CONVERT(DECIMAL, COUNT(DISTINCT il1.Parent_Org)) / CONVERT(DECIMAL, 100)) * 100 AS PercentViolatingParentOrgs,
      sum(ProjGeogEnrollment)  as myCount
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
       left outer join   #myLoadSeq as z
            on z.OrgHNoCategory = il1.OrgHNoCategory
                  and z.StCtyCode = il1.StCtyCode
WHERE
      il1.OOPC_Grand_Total - il2.OOPC_Grand_Total < 16.5
      OR il3.OOPC_Grand_Total - il1.OOPC_Grand_Total < 16.5

Author

Commented:
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?

Author

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

Author

Commented:
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

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial