Skip to content
Advertisement

Need to get difference between these two as output

REQUIRED QUERY :

select all memberid from query 1 having higher count than query 2

the aim is to display all memberid who were leader/organiser/helper more times than they participate in event

how do i join these two select statement and view the required output

SELECT memberID, COUNT(*) AS cnt
    FROM
    (
        SELECT memberID FROM leader UNION ALL
        SELECT memberID FROM organiser UNION ALL
        SELECT memberID FROM helper
    ) t
    GROUP BY memberID;

=============

select memberid,count(*) as cnt2
from eventmember group by memberid;

=======

Advertisement

Answer

another, “all_in_one” solution

select
    memberid,
    count(*) as total_cnt,
    sum(case when type = 'eventmember' then 1 else 0 end) as eventmember_cnt,
    sum(case when type = 'eventmember' then 0 else 1 end) as other_cnt
from
    (   select memberid, 'leader' as type
        from leader union all

        select memberid, 'organiser' as type
        from organiser union all

        select memberid, 'helper' as type
        from helper union all

        select memberid, 'eventmember' as type
        from eventmember
    ) t
group by
    memberid
having
    sum(case when type = 'eventmember' then 1 else 0 end)
    < sum(case when type = 'eventmember' then 0 else 1 end)

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement