probelm of having clause

padmapriya patruni

11y1k

hi ,

i have one problem with this query please help me. if iam using having cluase iam not getting records . if iam run from select to group by iam getting results . please help me how to use this query with having clause

SELECT
CONCAT(s.Skill,'(',es.Experience,' Months',')')AS Skills
FROM projectemployee p
LEFT OUTER JOIN employeeskills es ON p.EmployeeId =es.EmployeeId
LEFT OUTER JOIN skilllookup s ON es.Skill =s.SkillId
WHERE CURDATE() BETWEEN p.StartDate AND p.EndDate
GROUP BY p.EmployeeId
HAVING SUM(p.AllocationPercentage) < 100;

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi11y ago
Hi,
Please verify the data in SUM(p.AllocationPercentage) column, using query which have provided. any record has SUM(p.AllocationPercentage) column less than 100?
0
padmapriya patruni11y ago
hi jignesh without having clause I am getting results. how can i use this condition SUM(p.AllocationPercentage) < 100;please help me
0
Jignesh Trivedi11y ago
Hi,
I think there is problem in group by cluase, you just group by only one field employeeid. it might be possible there are many record present for single employee and sum of AllocationPercentage is higher than 100.
To identify the issue try query without having and put sum column with select
SELECT
CONCAT(s.Skill,'(',es.Experience,' Months',')')AS Skills,SUM(p.AllocationPercentage)
FROM projectemployee p
LEFT OUTER JOIN employeeskills es ON p.EmployeeId =es.EmployeeId
LEFT OUTER JOIN skilllookup s ON es.Skill =s.SkillId
WHERE CURDATE() BETWEEN p.StartDate AND p.EndDate
GROUP BY p.EmployeeId
hope this will help you.
0