CODEWITHSUNDEEP
sqlitegroup-byduplicatesdistinct
Resurgent
Resurgent

Reputation: 555

SELECT DISTINCT and GROUP BY returning duplicates

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?

Upvotes: 1

Views: 2237

Answers (3)

Juraj
Juraj

Reputation: 768

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

Upvotes: 2

Resurgent
Resurgent

Reputation: 555

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

Upvotes: 0

CL.
CL.

Reputation: 180010

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.)

Upvotes: 0

Related Questions