Retrieve diff columns while not included wthin Group by

Question

I see some student write this code on the Q&A section : I'm wondering how this code will work without using the column hire_date as well within "Group By", how you can group data based on one column while the results we want to retrieve include another column that probably contains diff values for each row "first_name" (I don't think all group of 'first_name' have the same hire_date)

SELECT
COUNT(first_name), first_name, hire_date
FROM
employees
WHERE
hire_date > '1999-01-01'
GROUP BY first_name
HAVING COUNT(first_name) < 200;

Answer 1

Hi Elmehdi!
Thanks for reaching out.

Actually, it is not compulsory to use the hire_date column in the GROUP BY clause. You use this column which you would like to group the result set by.

Hope this helps.
Best,
Tsvetelin

Answer 2

No, I mean we can't retrieve in the same time a column with multiple records hire_date and a column that return only a single value COUNT(first_name) without grouping the records by this column (hire_date)

Actually, I see the answer to my question farther in the course, here:

https://learn.365datascience.com/courses/sql/using-aggregate-functions-with-joins/

We can't retrieve by SELECT an aggregate function and another column without grouping by this column in other versions of SQL.

Thanks.

Answer 3

Hi Elmehdi!
Thanks for reaching out.

Yes, we have to use this column in the GROUP BY clause.

I am glad that you have found the lecture which describes this concept.

Best,
Tsvetelin

Retrieve diff columns while not included wthin Group by

Submit an answer

related questions