Group by "outer reference" error in SQL server-CodePudding

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!

CodePudding user response：

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