CODEWITHSUNDEEP
mysqldatabase
LearningProcess
LearningProcess

Reputation: 613

How to get the row with the maximum value of a summed column

Here is what my database looks like: https://dev.mysql.com/doc/employee/en/sakila-structure.html

My goal is to find the department(s) where employees work the longest.

Here is what I have:

SELECT dept_no, MAX(SUM(DATEDIFF(
                                 case when to_date = '9999-01-01' then current_date else to_date end,
                                 from_date))) as amount
FROM dept_emp d, employees e
WHERE d.emp_no = e.emp_no
GROUP BY d.dept_no
ORDER BY amount DESC

I'm also getting the "Invalid use of group function". I've been over this a few times and can't get it correct. I feel like there's an issue doing the MAX(SUM(DATEDIFF(, but that's the only approach I can think of. Any help would be greatly appreciated!

Thanks for reading!

----------------------------------------------------------------------------------------------------------------------------------- Update:

I'm starting to think I might need the max(avg(sum(datediff( now..

Upvotes: 1

Views: 30

Answers (1)

sagi
sagi

Reputation: 40481

You can't use two aggregation function on each other, and even if you could it wouldn't do anything.

You can drop the max from your query, and just limit the results by 1 to get the biggest one,like this:

SELECT d.dept_no,
       SUM(DATEDIFF(case when to_date = '9999-01-01'
                             then current_date else to_date end,
                    from_date))) as amount
FROM dept_emp d, employees e
WHERE d.emp_no = e.emp_no
GROUP BY d.dept_no
ORDER BY amount DESC
LIMIT 1;

If the maximum is what you are looking for, just replace them.

Upvotes: 1

Related Questions