Can’t use HAVING clause properly in SQL

I’m having problem with my SQL query.

SELECT TO_CHAR(e.hire_date, 'MON') AS "Month",
       DECODE (e.department_id, 50, 'Shipping',
                                    'Other') AS "Department",
       MAX(e.salary),
       MIN(e.salary),
       TO_CHAR(AVG(CASE 
                    WHEN e.job_id LIKE '%MAN%' THEN e.salary
                    ELSE 0
                    END)),
       COUNT(e.department_id)
FROM hr.employees e
GROUP BY TO_CHAR(e.hire_date, 'MON'), DECODE (e.department_id, 50, 'Shipping',
                                    'Other')
ORDER BY 2 DESC, DECODE(TO_CHAR(e.hire_date, 'MON'), 'JAN', 1,
                                             'FEB', 2,
                                             'MAR', 3,
                                             'APR', 4,
                                             'MAY', 5,
                                             'JUN', 6,
                                             'JUL', 7,
                                             'AUG', 8,
                                             'SEP', 9,
                                             'OCT', 10,
                                             'NOV', 11,
                                             'DEC', 12)

This is my first post, so i’m sorry if the layout isn’t appropriate. I have to filter the grouped results. If there is at least one employee with “MAN” job_id in each row(group in this instance) then the row must be displayed, if there is none, then the whole row(group) shouldn’t be displayed at all. it needs to be done with HAVING clause i suppose, but i always get errors… anyone who could help, please?

Image of Result The Code Image

Answer

If there is at least one employee with “MAN” job_id in each row(group in this instance) then the row must be displayed,

You can use:

having sum(case when e.job_id LIKE '%MAN%' then 1 else 0 end) > 0

Advertisement

Answer