Column in iif function invalid in the select list because it is not contained in either an aggregate-CodePudding

Trying to use an unincluded field in an IIF function and get the error that the field is not part of the GROUP BY CLAUSE.

SELECT IIF(T1.a = 0, "none", T1.type) AS type, COUNT(*) AS mycnt
FROM T1
GROUP BY type

Why? In the case where T1.a is 0 there is no value in T1.type so I want to fill it with something to group on.

I understand perfectly well that SELECTed columns are required to be in the GROUP BY clause so that it knows what value goes in the GROUPed record. But I am not asking it to give me an unknowable value of T1.a. I just want it to consider the value of T1.a when gathering the records to then aggregate.

Any other ways to fill this? I have some pretty intense WHERE clauses so the idea of SELECTing from a differential UNION looks kind of messy.

CodePudding user response：

The main issue is about functional dependence with respect to the GROUP BY terms.

Your GROUP BY terms (type) is not referring to the derived column AS type, but to T1.type, which means T1.a is not functionally dependent on the GROUP BY terms, unless T1.type happens to be the PRIMARY KEY or UNIQUE and NOT NULL, and your database understands that kind of functional dependence. Some databases do not understand this (part of the SQL standard).

If you meant to GROUP BY derived_type, you can do something more like this:

WITH T1a AS (
         SELECT IIF(T1.a = 0, 'none', T1.type) AS type
           FROM T1
     )
SELECT type
     , COUNT(*) AS mycnt
  FROM T1a
 GROUP BY type
;

The fiddle

Test case:

CREATE TABLE T1 ( type VARCHAR(10), a int );

WITH T1a AS (
         SELECT IIF(T1.a = 0, 'none', T1.type) AS type FROM T1
     )
SELECT type
     , COUNT(*) AS mycnt
  FROM T1a
 GROUP BY type
;

CodePudding user response：

You need to have same GROUP BY expression in the select clause. You can have code as given below:

SELECT IIF(T1.a = 0, 'none', T1.type) AS type, COUNT(*) AS mycnt
FROM T1
GROUP BY IIF(T1.a = 0, 'none', T1.type)