Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Hello I have this query

SELECT perf_prog.date, MAX(perf_prog.end_time) , ADDTIME((shift_time.out), "01:00:00") FROM perf_prog 
INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id
WHERE perf_prog.emp_id = 920   GROUP BY perf_prog.date  
and this is the output of the query
But i want to list only those value of end_time which is greater than shift_time.out
so i did this
SELECT perf_prog.date, MAX(perf_prog.end_time) , ADDTIME((shift_time.out), "01:00:00") FROM perf_prog 
INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id
WHERE perf_prog.emp_id = 920 AND MAX(perf_prog.end_time) > ADDTIME((shift_time.out), "01:00:00")   GROUP BY perf_prog.date  
but i'm receiving error 
Error Code: 1111
Invalid use of group function
and upon searching stackoverflow the answer that solved their problem is move the condition to having clause
so i got this
SELECT perf_prog.date, MAX(perf_prog.end_time) , ADDTIME((shift_time.out), "01:00:00") FROM perf_prog 
INNER JOIN Shifts ON perf_prog.emp_id = Shifts.emp_id 
INNER JOIN shift_time ON Shifts.id = shift_time.id
WHERE perf_prog.emp_id = 920 GROUP BY perf_prog.date  
HAVING MAX(perf_prog.end_time)   > ADDTIME((shift_time.out), "01:00:00")
and now the problem is Unknown column 'shift_time.out' in 'having clause'
Am i following the correct solution for my needs? Thank you. ive been trying to figure out the solution but i can't make it work
You need an aggregation on the column.  I think this might be what you want:
SELECT pp.date, MAX(pp.end_time),
       MAX(ADDTIME((st.out), '01:00:00'))
FROM perf_prog pp INNER JOIN
     Shifts s
     ON pp.emp_id = s.emp_id INNER JOIN
     shift_time st
     ON s.id = st.id
WHERE pp.emp_id = 920
GROUP BY pp.date  
HAVING MAX(pp.end_time) > MAX(ADDTIME((st.out), '01:00:00))
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.