SELECT DISTINCT and GROUP BY returning duplicates

Question

I'm trying to select a distinct HourlyRate from a table, and then group the resulting HourlyRate by a FECode (basically a person). One person may have 2 or 3 rates over time, but the results that are returning involve the same HourlyRate being repeated for the same FECode.

SELECT DISTINCT Cost/Hours As HourlyRate, Date, FECode
FROM Table1
WHERE HourlyRate != ''
GROUP BY HourlyRate, FECode
ORDER BY FECode

The result looks like as follows:

HourlyRate, Date, FECode
215.00, 2017-04-06, AAA
215.00, 2017-04-27, AAA
225.00, 2017-06-16, AAA

The data from Table1 is as follows:-

Date, FECode, Cost, Hours
2017-04-06, AAA, 236.5, 1.1
2017-04-27, AAA, 43, 0.2
2017-06-16, AAA, 247.5, 1.1

Clearly, in this example, the second result of 215.00 should not be returning, but it is. How do I stop this from happening?

We need both sample table data and the expected result. (Formatted text, please.) — jarlh, Jan 19 '18 at 08:36
There is no duplicate row in your example output (also: using `group by` without an aggregate doesn't really make sense) — , Jan 19 '18 at 08:38
I've added the table data to the original question. Yes, there is a duplicate row in the dataset, because there should only be one instance of 215.00. If I remove Date from the SELECT column, I still get 2 instances of 215.00, AAA. So yes, there is a duplicate. — Resurgent, Jan 19 '18 at 08:41
Possible duplicate of [mySQL select one column DISTINCT, with corresponding other columns](https://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns) — MrPerry95, Jan 19 '18 at 08:46

Juraj · Accepted Answer · 2018-01-24T07:27:27.857

2

The result is ok because DISTINCT remove the line which match on "full set of columns". The Cost/Hours is number which is divide and the result looks like round number (but the number is not the same), therefore it did not match as the same number. try use this, and do not forget the remove date column:

SELECT cast(Cost/Hours as text) As HourlyRate, FECode
FROM Table1
WHERE HourlyRate != ''
ORDER BY FECode

edited Jan 24 '18 at 07:27

answered Jan 19 '18 at 08:40

Juraj

738
2
9
26

Yes, but I only expect to see one instance of 215.00, one instance of 225.00. See my comment above, where even if I remove date, I still get two results of 215.00, AAA which is not the result I expect. – Resurgent Jan 19 '18 at 08:42
Thank you for your edit. When I run this query (I tried this before), I get 5 results: 2 x 215.00, AAA and 3x 225.00, AAA – Resurgent Jan 19 '18 at 08:45
I did not notice that the 215.0 is calculated field. Pleas try this DISTINCT(Cost/Hours) As HourlyRate – Juraj Jan 19 '18 at 08:51
I think that I know where the problem is. The 215.00 is rounded number and therfore there is not distinc on whole number. I will edit the answer – Juraj Jan 19 '18 at 08:55

score 0 · Answer 2 · answered Jan 19 '18 at 08:54

These two values are not equal:

SELECT 236.5/1.1 = 43/0.2;
0

There actually is a difference:

SELECT 236.5/1.1 - 43/0.2;
-2.8421709430404e-14

See Is floating point math broken?

You have to round the result.

(And using the column Date with this GROUP BY does not make sense.)

score 0 · Answer 3 · answered Jan 19 '18 at 09:02

0

The following query returns the expected result:-

SELECT ROUND(Cost/Hours, 2) As HourlyRate, Date, FECode FROM Table1 WHERE HourlyRate!= '' GROUP BY FECode, HourlyRate ORDER BY FECode ASC

answered Jan 19 '18 at 09:02

Resurgent

525
2
9
20

SELECT DISTINCT and GROUP BY returning duplicates

3 Answers3