CODEWITHSUNDEEP
sqlsql-server
Nikul Panchal
Nikul Panchal

Reputation: 711

Why am I getting "column invalid" error

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;

Upvotes: 0

Views: 88

Answers (3)

aljassi
aljassi

Reputation: 246

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';

Upvotes: 0

cdaiga
cdaiga

Reputation: 4939

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;

Upvotes: 1

Risto M
Risto M

Reputation: 2999

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;

Upvotes: 0

Related Questions