BeGreen BeGreen - 11 months ago 69
SQL Question

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

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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download