365 Days of Daily Coding: Day 112

Part 1

What happens if I GROUP BY a column that is not in the SELECT statement? Why does this happen?

When a GROUP BY column that is not in the SELECT statement is used, it will throw an error.

By the order of operations in SQL, SELECT or selecting columns run first before SQL groups them. If a column that is not in select statement is used, GROUP BY cannot locate the column and hence it will throw an error.

I found this very interesting post which will help to understand the concept more; Medium article and Blog Post.

Part 2

So I took sometime to understand the blog post by Roland Bouman. And I now relalise the answer to the question is not as simple as the one shared above. How GROUP BY behaves depends on the brand of the rdbms.

For instance, in Oracle, the above answer stands true. If a column is selected in the SELECT clause but not included in the GROUP BY clause, there will be an error. However, with MySql, the query will run without any error.

Calculating Aggregates for a group of rows

SELECT species, 
GROUP_CONCAT(animals), -- makes a list of animals per species
MIN(birth),
MAX(birth)
FROM Table_1
GROUP BY species

Output:

id no.	Species	GROUP_CONCAT(animals)	MIN(birth)	MAX(birth)
1	bird	Chirpy, Whistler	1997-12-09	1998-09-11
2	cat	Fluffy, Claws	1993-02-04	1994-03-17
3	dog	Buffy,Fang,Bowser	1979-08-31	1990-08-27
4	hamster	Puffball	1999-03-30	1999-03-30
5	snake	Slim	1996-04-29	1996-04-29

• The GROUP_CONCAT function concatenates all the names under each species. The function is a special one only available in MSSQL.
• The MAX and MIN functions take the oldest and youngest pets from the species group.

Running into trouble with GROUP BY

SELECT species, 
MIN(birth),
MAX(birth),
birth
FROM Table_1
GROUP BY species

Running the above expression in oracle will give the following error:

ERROR at line 4:
ORA-00979: not a GROUP BY expression

However, running the above expression will give the below output.

Output

species	MIN(birth)	MAX(birth)	birth
bird	1997-12-09	1998-09-11	1998-09-11
cat	1993-02-04	1994-03-17	1993-02-04
dog	1979-08-31	1990-08-27	1989-05-13
hamster	1999-03-30	1999-03-30	1999-03-30
snake	1996-04-29	1996-04-29	1996-04-29

Here in this output, the birth column randomly assgins a value from the corresponding species column although it does not make sense.