Why we need to add all fields to GROUP BY Clause?

Question

I have a table

test1

and a view

view1

, and i'm wondering why I get this results:

CREATE TABLE test1 (

    A_ VARCHAR(10),

    B_ VARCHAR(10),

    C_ VARCHAR(10),

    D_ VARCHAR(10)

);



CREATE VIEW view1 AS

SELECT

    CASE

        WHEN D_ in ('false') THEN LEFT(A_, CHARINDEX('(', A_) -1)

        ELSE D_

    END AS D,

    MIN(B_) B,

    CASE

        WHEN C_ IS NOT NULL THEN '0'

        ELSE C_

    END AS C

FROM test1

GROUP BY

    D_,

    B_,

    C_;

Column 'test1.A_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know I have to add

A_

in the

GROUP BY

section, but why since it won't be showed in the View?
Also, why do I have the same error if I remove the

GROUP BY

clause?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Since the _A is used in the LEFT(A_, CHARINDEX('(', A_) -1) you have to include it in the GROUP BY. It does not matter that it is in the CASE. The only situation that we do not have to add the attribute to GROUP BY is when the attribute is part of an aggregate function.

Why we need to add all fields to GROUP BY Clause?

Sign up