I'm writing a having clause to check if an aggregated value is contained in a sub-query. When I use an alias everything works, however if I chose not to use one the having clause returns no matches. For the sake of testing I removed the sub-query and just used some numbers and using the aggregated value in the having clause was fine, but if I use the sub-query it doesn't work unless I use the alias.

The clause I have a problem is the second one (after the or). When i use count(challenge_id) it doesn't work. If I use some numbers instead of the sub-query (count(challenge_id) in (1,2,3,4,5)) it works fine, however if I use the sub-query I get no results unless I use the alias. Hope I'm clear enough...

select c.hacker_id, name, count(challenge_id) as chal_count from challenges as c
    join hackers as h
    on c.hacker_id = h.hacker_id
    group by c.hacker_id,name
    having
    chal_count = 
        (select max(chal_count) from 
            (select count(challenge_id) as chal_count from challenges as c
            join hackers as h
            on c.hacker_id = h.hacker_id
            group by c.hacker_id,name) as d)
    or
    chal_count in 
        (select chal_count from 
            (select count(challenge_id) as chal_count from challenges as c
            join hackers as h
            on c.hacker_id = h.hacker_id
            group by c.hacker_id,name) as d
            group by chal_count
            having count(chal_count) = 1)
    order by chal_count desc, c.hacker_id


1 Answers

0
Eric On

If you understand SQL order of operation, you'll know why your code doesn't work. https://www.periscopedata.com/blog/sql-query-order-of-operations

HAVING is evaluated before SELECT, and you put the alias inSELECT, so you can't use the alias in HAVING. Repalce chal_count with COUNT(challenge_id).