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?

The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." — jarlh, Oct 03 '17 at 09:46
No, don't do `GROUPE BY A, C, D` because they are aliases so it will output an error. — BeGreen, Oct 03 '17 at 09:48

score 2 · Accepted Answer · answered Oct 03 '17 at 09:47

2

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.

answered Oct 03 '17 at 09:47

Radim Bača

10,646
1
19
33

Just finding this weird that we have to add it. I know it's being used, but it's not like it's a useful information this the output of `SELECT * FROM view;` won't show `A_` at all. – BeGreen Oct 03 '17 at 09:50
@begreen You have to realize that there can be many rows with many different `A_` values for one combination of `D_, B_, C_`. The question is: which value should be used in such case? – Radim Bača Oct 03 '17 at 09:53
Ok, maybe I was blind by my real code where there can be only one `A_`. But then why do I get the same error If I remove `GROUP BY`? – BeGreen Oct 03 '17 at 09:55
1

@BeGreen Once you have an aggregation function behind `SELECT`, there can be only attributes which are also behind `GROUP BY` outside of the aggregate function. – Radim Bača Oct 03 '17 at 09:58
Ok thank you, clear answer. – BeGreen Oct 03 '17 at 10:05

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

1 Answers1