Why am I getting "column invalid" error

Question

I have converted mysql query to a SQL Server T-SQL query, and when I run this query, I get an error:

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

Here is my query, can anyone please tell me why I am getting this error ?

SELECT 
    t.id, t.value, t.branch_id, k.name 
FROM  
    tb_target as t 
LEFT JOIN 
    tb_keyindicator as k ON k.id = t.keyindicator_id 
WHERE 
    t.branch_id IN (241) 
    AND t.period >= '2017-09' 
    AND t.period < '2017-10' 
GROUP BY 
    branch_id;

Because of the exact reason the error says... You can't use GROUP BY unless all the columns in the SELECT list are a) also in GROUP BY or b) are used with an aggregate function. Think about this logically, how could you have a list of items and a summary on the same line in the same column? — Jacob H, Jan 31 '18 at 14:01
@JacobH Or if the other columns are functionally determined by one of the group by columns, e.g. a primary key — Tim Biegeleisen, Jan 31 '18 at 14:02
Can you please help me how can i resolve this error ? How this query can be run ? — Nikul Panchal, Jan 31 '18 at 14:02
@NikulPanchal Why do you think you even need `GROUP BY` here? — Tim Biegeleisen, Jan 31 '18 at 14:04
I'm with @TimBiegeleisen. What are you trying to accomplish here? I'm guessing a window function is what you really want. — Jacob H, Jan 31 '18 at 14:08

score 1 · Answer 1 · answered Jan 31 '18 at 14:03

1

You have to include all the columns in the select that are not aggregated in the group by clause. So your query should be:

SELECT t.id, t.value, t.branch_id, k.name 
FROM tb_target as t 
LEFT JOIN tb_keyindicator as k ON k.id = t.keyindicator_id 
WHERE t.branch_id IN (241) AND t.period >= '2017-09' AND t.period <  '2017-10' 
GROUP BY t.id, t.value, t.branch_id, k.name;

answered Jan 31 '18 at 14:03

cdaiga

4,861
3
22
42

Of course, if you're going to group by on all columns in the select list and not use any aggregates at all, it's clearer to switch to `DISTINCT` and remove the group by clause entirely. – Damien_The_Unbeliever Jan 31 '18 at 14:55
`GROUP BY` is synthaxically simpler than using `DISTINCT` in this case. – cdaiga Jan 31 '18 at 15:56
@cdaiga - from a `SELECT` without a `GROUP BY` nor a `DISTINCT`. To get distinct values, you can a) add the word `DISTINCT` to the `SELECT` clause or b) add a `GROUP BY` clause that lists all columns from the `SELECT` clause again. How is (b) simpler? – Damien_The_Unbeliever Feb 02 '18 at 06:28

score 0 · Answer 2 · answered Jan 31 '18 at 14:02

If grouping is correct, you have to apply aggregate function to following fields: t.id, t.value, k.name. See more discussion about similar error on another SO topic here.

Example:

SELECT MIN(t.id), MIN(t.value), t.branch_id, MIN(k.name)
FROM tb_target as t 
LEFT JOIN tb_keyindicator as k ON k.id = t.keyindicator_id 
WHERE t.branch_id IN (241) AND t.period >= '2017-09' AND t.period < '2017-10' 
GROUP BY branch_id;

score 0 · Answer 3 · answered Jan 31 '18 at 14:39

SELECT t.id, t.value, t.branch_id, k.name 
FROM tb_target as t 
LEFT JOIN tb_keyindicator as k ON k.id = t.keyindicator_id 
WHERE t.branch_id IN (241) AND t.period >= '2017-09' AND t.period < '2017-10' 
GROUP BY branch_id;

As your query is written, you dont need the

group by

part. So your query should be like this.

SELECT t.id, t.value, t.branch_id, k.name 
FROM tb_target as t 
LEFT JOIN tb_keyindicator as k ON k.id = t.keyindicator_id 
WHERE t.branch_id IN (241) AND t.period >= '2017-09' AND t.period < '2017-
10';

Why am I getting "column invalid" error

3 Answers3