Why Group By including a particular column record when there are similar other column records while giving output

[Admin]

Here is the problem:

What is the pay type for all the job codes that start with ’03’? The code has been started for you, but you will need to program the fourth and fifth lines yourself before running the query.

Here is my code:

SELECT Job_code,pay_type
FROM salary_range_by_job_classification
WHERE job_code LIKE '03%'

Source: https://www.coursera.org/learn/sql-for-data-science

Next, I apply Group By option:

Select job_code,pay_type
FROM salary_range_by_job_classification
WHERE job_code LIKE '03%'
GROUP BY Pay_Type

My query is why 0382 Job_Code shows up? Since the answer is supposed to be in one row, and there is a need to fill job_code field as the same is with SELECT, a compromise made?

---

Reply

---

With Group By, SQLite and some versions of MySQL allow unaggregated columns in the SELECT. It is a bad practice and better be avoided.

Essentially, the value chosen for job_code is undefined.

https://www.sqlite.org/lang_select.html#bareagg

[Author]

Posts