CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2816983
user2816983

Reputation:

How to use group by in sql query?

SELECT DISTINCT 
           t1.name as t1_name, 
           MAX(t1.unit) as t1_unit, 
           MAX(t1.id_producer_goods) AS hi_id_producer_goods, 
          t2.name as t2_name 
FROM Table1 t1 
    left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE t1.id='23'  
GROUP BY t1.name

When I run the query, I get the following error:

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

How to write this query?

Upvotes: 2

Views: 4276

Answers (4)

Suraj Singh
Suraj Singh

Reputation: 4059

I don't think you need DISTINCT keyword here .

           SELECT 
           t1.name as t1_name, 
           MAX(t1.unit) as t1_unit, 
           MAX(t1.id_producer_goods) AS hi_id_producer_goods, 
          t2.name as t2_name 
FROM Table1 t1 
    left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE t1.id='23'  
GROUP BY t1.name,t2.name

Upvotes: 0

Amit
Amit

Reputation: 15387

Try this

SELECT 
DISTINCT 
t1.name as t1_name, 
MAX(t1.unit) as t1_unit, 
MAX(t1.id_producer_goods) AS hi_id_producer_goods, 
t2.name as t2_name FROM Table1 hi 
left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE 
t1.id='23'  
GROUP BY t1.name,t2.name

You are using aggregate function with 2 columns then you need to group on both columns

Upvotes: 0

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

SELECT 
t1.name as t1_name, 
MAX(t1.unit) as t1_unit, 
MAX(t1.id_producer_goods) AS hi_id_producer_goods, 
t2.name as t2_name FROM Table1 hi 
left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE 
t1.id='23'  
GROUP BY t1.name,t2.name

You need to group by all fields that are not used in AGG functions. etc MAX

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

The error is pretty clear, either use an aggregate function with the t2.name or add it to the GROUP BY, it depends on the desired results you are looking for:

SELECT 
  t1.name as t1_name, 
  t2.name as t2_name,
  MAX(t1.unit) as t1_unit, 
  MAX(t1.id_producer_goods) AS hi_id_producer_goods
FROM Table1 hi 
left join Table2 t2 on t1.id_web_site=t2.id_web_site 
WHERE t1.id='23'  
GROUP BY t1.name, t2.name;

The error makes sense, because it has to know which value to select from the t2.name for each group of t1.name? should it select the max, min, etc. Otherwise GROUP BY it.

Also, remove the DISTINCT there is no need for it with GROUP BY.

Upvotes: 5

Related Questions