CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Azima
Azima

Reputation: 4141

Having clause is not filtering records mysql

I am trying to get total sum of hours for each project_type for a particular year, and hence a single row as a result.

Here's my SQL query:

select SUM(total_hours) as hours, year(task_completion) as year
from task_details
group by project_type_id
having year = '2018' AND project_type_id = 10

Also, here's how my data-set looks like:

project_type_id  |   total_hours   |  task_completion
-------------------------------------------------------
10               |   5             |  2018-9-10
10               |   4             |  2018-9-11
10               |   10            |  2017-9-10
10               |   2             |  2016-9-10
11               |   9             |  2017-9-10
14               |   8             |  2017-9-11

The query gives output as :

hours  |  year
---------------
21     |  2018

But I expected it to be 9.

What is wrong with the query?

Upvotes: 1

Views: 1026

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Your query logic is simply off. You have a GROUP BY use different unaggregated columns in the SELECT. You have columns in the HAVING that are neither aggregated nor in the GROUP BY. MySQL extends its syntax to allow this. However, the query (and results) do not make sense.

Your query should look like this:

select sum(total_hours) as hours, year(task_completion) as year
from task_details
where project_type_id = 10
group by year
having year = 2018;

What is the difference? Note:

  • The comparison for project_type_id is in the WHERE clause before aggregation, rather than after aggregation.
  • The GROUP BY clause contains the unaggregated columns from the SELECT.
  • year is a number, so the comparison is to a number, not a string.

I would advise you to do all the comparisons before the aggregation -- so no having clause is needed:

select sum(total_hours) as hours,
       year(task_completion) as year
from task_details
where project_type_id = 10 and
      task_completion >= '2018-01-01' and
      task_completion < '2019-01-01'
group by year;

Note this version does not use year(). Functions on columns can impede the use of indexes. This version can use an index on (project_type_id, task_completion).

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You need to use condition for year and project_type_id in the where instead of having, otherwise it was summing values for all the year:

select project_type_id, 
       SUM(total_hours) as hours, 
       year(task_completion) as year_completion
from task_details
WHERE year(task_completion) = 2018 
 and project_type_id = 10
group by project_type_id

Upvotes: 1

Related Questions