1

I am learning sql join and would like to clear an idea. I understand that all the select columns should present in group by clause. For example,

select a, b
from table_one
group by a,b;

I want to join two tables, something similar to this -

select table_one.a
from table_one, table_two
where table_one.id = table_two.id
group by table_two.c

The question is - should I put table_two.c in select since it is in group by clause?

Shikiryu
  • 10,180
  • 8
  • 49
  • 75
Ravi Kumar
  • 98
  • 6

2 Answers2

2

A group by clause is neede, if you have an analytical function. Simple example:

select order_date, sum(orders)
from orders_table
group by order_date;

It makes logically sense, to group only on columns, that are also in your select clause. Otherwise, you could have weird data for the end user. Example:

select order_date, sum(orders)
from orders
group by country. order_date;

This would give you a separate row for each date and country, but you only see the date in the result.

So the simple rule ist: add all columns to the group by statement that do not use an analytical function (i.e. min(), max(), avg(), count()) and do not use columns in the group by statement that are not also present in the select clause. This does not change when joining. Grouping is completely independant from joins. THe only thing that matters is, which columns you use.

drunken_monkey
  • 1,760
  • 1
  • 12
  • 14
1

When joining tables you can use the JOIN-statements instead

Example with your query:

SELECT t1.a
FROM table_one t1 /*(t1 is the alias for the table)*/
INNER JOIN table_two t2 ON t1.id = t2.id
GROUP BY t2.c

You don't need to put the t2.c in your select, if you don't specifically want it in the results. Since you are only selecting t1.a, that is all you're gonna get.

Another answer here on SO regarding GROUP BY is worth reading aswell. :) https://stackoverflow.com/a/1591976/1025823

Community
  • 1
  • 1
NoLifeKing
  • 1,909
  • 13
  • 27