CODEWITHSUNDEEP
mysql
nickcoxdotme
nickcoxdotme

Reputation: 6697

MySQL: having rounded sum not equal to integer

I have a query in which I compare a rounded sum to an integer, and I'm receiving unexpected results.

SELECT assignments.*
  FROM assignments
  INNER JOIN time_entries
    ON time_entries.assignment_id = assignments.id
  WHERE assignments.organization_id = 2
  AND assignments.allocation_mode = 'fixed'
  AND (fixed_hours is not null)
  HAVING round(sum(time_entries.scheduled_hours)) != round(assignments.fixed_hours);

It returns an assignment with fixed_hours of 20. The column is a float.

When I select the sum of the time_entries for that match that record, I get 20.000000298023224. When I call round on that, I get 20:

SELECT
  round(sum(scheduled_hours))
  FROM time_entries
  WHERE assignment_id=112869;

And SELECT round(fixed_hours) from assignments where id=112869 also gives 20.

And of course select round(20.000000298023224) = round(20); returns 1.

So what's wrong with my query that that record is being returned?

Upvotes: 0

Views: 72

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35583

Nice try at query summarization :), but you need a group by...

SELECT
      assignments.*
FROM assignments
INNER JOIN (
      SELECT
            assignment_id
          , ROUND(SUM(time_entries.scheduled_hours)) sum_hrs
      FROM time_entries
      GROUP BY
            assignment_id
) te ON assignments.id = te.assignment_id
WHERE assignments.organization_id = 2
AND assignments.allocation_mode = 'fixed'
AND (fixed_hours IS NOT NULL)
AND te.sum_hrs <> ROUND(assignments.fixed_hours)
;

Upvotes: 3

Related Questions