T.r. T.r. -4 years ago 81
MySQL Question

In Join condition I want to use group by and having clause but get error? How to use group by and having clause

When I want to fetch data from table it_Service_ticket. The error is


Unknown column 'it_service_ticket.xetr' in 'having clause'


how to use group by and having condition? Please help me

SELECT Assignedto,COUNT(Assignedto) as TC
,CONCAT(count(case when STATUS = 'CLOSE' then 1 else null end) * 100 / count(1), '%') as SC
,CONCAT(count(case when STATUS = 'PENDING' then 1 else null end) * 100 / count(1), '%') as PC
,SUM(TIMESTAMPDIFF(MINUTE,Request_Date, Xetr))/60 as WH ,(540-sum(TIMESTAMPDIFF(MINUTE,Request_Date, Xetr)))/60 as VH,
COUNT(Feedback_Rate)/COUNT(Assignedto)*100 as Feed_Percent,
SUM(Feedback_Rate)/(count(Feedback_Rate)*5)*5 as AVG_Feedback
FROM `it_service_ticket`
INNER JOIN `it_problem`
ON `it_service_ticket`.`it_problem_id`=`it_problem`.`it_problem_id`
INNER JOIN `city_master`
ON `it_service_ticket`.cityid=`city_master`.city_id

GROUP BY Assignedto
HAVING `it_service_ticket`.`xetr` BETWEEN '2017-01-01 12:00:00 AM' AND '2017-03-31 12:00:00 PM'
;

Answer Source

I think you just want where, not having:

SELECT Assignedto, COUNT(Assignedto) as TC,
       CONCAT(AVG(STATUS = 'CLOSE') * 100, '%') as SC,
       CONCAT(AVG(STATUS = 'PENDING') * 100, '%') as PC,
       SUM(TIMESTAMPDIFF(MINUTE, Request_Date, Xetr))/60 as WH ,
       (540-SUM(TIMESTAMPDIFF(MINUTE, Request_Date, Xetr)))/60 as VH,
       COUNT(Feedback_Rate)/COUNT(Assignedto)*100 as Feed_Percent,
       SUM(Feedback_Rate)/(count(Feedback_Rate)*5)*5 as AVG_Feedback
FROM it_service_ticket st INNER JOIN
     it_problem` p
     ON st.it_problem_id = p.it_problem_id INNER JOIN
     `city_master` cm
     ON st.cityid = cm.city_id
WHERE st.xetr BETWEEN '2017-01-01 00:00:00' AND '2017-03-31 12:00:00'
GROUP BY Assignedto;

In general, only use HAVING when you are filtering on aggregation functions.

Notes:

  • I simplified the logic for calculating ratios. You might want to use format() to get a particular number of decimal places.
  • Don't use backticks if they are not necessary. They just make the query harder to write and to read.
  • Similarly, table aliases make the query easier to write and to read.
  • Use 24-hour time formats -- much less prone to mistakes.
  • I question whether the calculation for Feed_Percent is correct. COUNT() counts the number of non-NULL values, so they ratio is likely to be 1.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download