SQL Group by returning wrong results

Question

I have a salary table in which I am trying to return determine the lowest salary earned and by which industry for each year however despite getting the correct lowest salary earned I am receiving the wrong industry name.

I am aware that it is due to the fact that I have utilized GROUP BY without placing a constraint(?) on it hence it is returning me the wrong value but I am not sure how I can solve it.

SALARY TABLE

salaryID
salaryAmount
salaryYear
industryName (ForeignKey)

Can someone please guide me on the right path?

    **(Problem Code)**
    SELECT MIN(S.salary), S.industryName, S.salaryYear
    FROM salary
    GROUP BY S.salaryYear;



    **(Attempted solution)**
    SELECT S.salary
    FROM salary
    INNER JOIN 
    SELECT (min(S1.amount)), S1.year, S1.industryName, S1.salaryId 
    FROM salary S1 
    GROUP BY S1.year
    ON S.salaryId = S1.salaryId);

Probably a duplicate of [sql-select-only-rows-with-max-value-on-a-column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, Oct 09 '19 at 16:57
Could you build an example on dbfiddle and show which result you will want to see for that example — nbk, Oct 09 '19 at 17:13
Use a proper `GROUP BY`. Why is `S1.industryName` is not in `GROUP BY`? — Eric, Oct 09 '19 at 17:19
@eric theres a syntax error in the solution where i cant even run the code to try — Yeo Bryan, Oct 09 '19 at 17:31

Eric · Answer 1 · 2019-10-09T18:42:28.177

1

Use a proper GROUP BY. Any non-aggregated columns must be included in GROUP BY.

SELECT MIN(amount), year
FROM salary
GROUP BY year

If you want to include industryName,

SELECT amount, year, industryName, salaryId
FROM (
    SELECT amount, year, industryName, salaryId 
        , ROW_NUMBER() OVER(PARTITION BY year ORDER BY amount) AS rn
    FROM salary
 ) a
 WHERE rn = 1

Pre-MySQL 8 version

SELECT *
FROM salary s
INNER JOIN (
    SELECT MIN(amount) AS minAmount, year
    FROM salary
    GROUP BY year
) m ON m.minAmount = s.amount AND m.year = s.year

edited Oct 09 '19 at 18:42

answered Oct 09 '19 at 17:23

Eric

3,165
1
19
25

i do not want to get the lowest salary for each specific industry for each year though i want to get the lowest salary earned by any of the industries for each year – Yeo Bryan Oct 09 '19 at 17:30
@YeoBryan industryName grouping removed. – Eric Oct 09 '19 at 17:35
i tried to copy the cope over but it says there is some syntax error and prevents me from using that code =/ – Yeo Bryan Oct 09 '19 at 18:02
I suppose your syntax error is due to your DB version is prior to 8, so can't use window analytic functions, can you? @YeoBryan – Barbaros Özhan Oct 09 '19 at 18:05
@BarbarosÖzhan i am actually on version "10.4.6-MariaDB" – Yeo Bryan Oct 09 '19 at 18:33
@YeoBryan Then why can't my `ROW_NUMBER()` function work? It should work. – Eric Oct 09 '19 at 18:41
@YeoBryan Just missing a comma. Debugging is part of programmer's job. Things like missing comma, mis-spelt column names, wrong column names, you should be able to figure out by yourself since you have the error and the data. We don't have the data or the error message. – Eric Oct 09 '19 at 18:44

Barbaros Özhan · Answer 2 · 2019-10-09T18:50:59.820

0

I think you need a self-join :

SELECT s1.industryName, s2.min_salary, s2.salaryYear
  FROM salary s1
  JOIN  
  (
   SELECT MIN(salary) as min_salary, salaryYear
    FROM salary
   GROUP BY salaryYear
   ) s2
    ON s1.salary = s2.min_salary
   AND s1.salaryYear = s2.salaryYear;

The Demo of this query with your sample data

edited Oct 09 '19 at 18:50

answered Oct 09 '19 at 17:43

Barbaros Özhan

59,113
10
31
55

its returning me the minimum salary but its still returning the wrong industry name, any idea how to rectify that – Yeo Bryan Oct 09 '19 at 18:01
is it possible to share your sample data within a fiddle ..? @YeoBryan – Barbaros Özhan Oct 09 '19 at 18:03
is this how it works? https://www.db-fiddle.com/f/fe6jqe4Bhv9F82UsnenJCb/0# . sorry i am not really good at this – Yeo Bryan Oct 09 '19 at 18:21
doesn't matter, but you have an extra `industryName`,which I commented out since I have no `industryName` column within the subquery, in your fiddle. Please have a look at : [Demo](https://www.db-fiddle.com/f/cjnupAqLjjPcKDETHGaWeV/0) which works well. – Barbaros Özhan Oct 09 '19 at 18:38

SQL Group by returning wrong results

2 Answers2