In SQL can GROUP BY be used with non-aggregate fields?

This is a general question on SQL.

Is GROUP BY always be used with some aggregate functions, such as COUNT() OR SUM()? Is there any edge case where you use GROUP BY with other normal fields?

Nope.

GROUP BY clause only makes sense with the aggregate function. GROUP BY clause without an aggregate function will give the same result as using DISTINCT, but DISTINCT is a much faster option.


To understand this it is good to know what happens when you use GROUP BY.

Take a look at this table named bugs:

id			course_id		exercise_id		reported_by
1			14				2				Alex
2			5				4				Tod
3			14				4				Tod
4			14				6				Tod
5			5				4				Alex
6			5				3				Roy
7			14				2				Roy
8			7				4				Alex
9			5				4				Roy
10			7				8				Tod

Now, we want to GROUP BY column reported_by. This gives us three sets of rows:

id			course_id		exercise_id		reported_by

1			14				2				Alex
5			5				4				Alex
8			7				4				Alex

6			5				3				Roy
7			14				2				Roy
9			5				4				Roy

2			5				4				Tod
3			14				4				Tod
4			14				6				Tod
10			7				8				Tod

And now, what happens when we want to SELECT data from, for example, column exercise_id? We have to look at each set of the rows above and decide what will be the single result. It does not make sense, right? How we can change 2, 4, 4 into a single value? This is where aggregate functions come in. For example, SUM() takes multiple numerical values and returns a single value.

This is why GROUP BY clause has to operate with the aggregate functions :slight_smile:

1 Like

Just out of curiosity, is a detailed answer like this one only available to PRO users? I’m still on trial.

Thank you very much! :slight_smile:

No, I am not a codecademy employee. I am simply a volunteer trying to help people learn how to code. So honestly, I don’t care if the user is PRO or not.

2 Likes