Skip to content
Advertisement

Group by a table then add another tables count into the first table grouped count

I want to group by the First table then add the result of the second and third table with the grouped count… But with a dummy servicecode

select servicecode, Count(*) as count from servicerequest
    where To_Char(CREATEDATE,'YYYY') = 2018
    and status = 50
    group by servicecode

        union

    select Count(*) as count from CERTREQUEST
     WHERE to_char(REQDATE,'yyyy') = 2018
    and service = 1

        union

    select Count(*) as count from LTR
     WHERE to_char(REQDATE,'yyyy') = 2018
    and Service = 1

Advertisement

Answer

Just select it in two other queries like that:

select servicecode, Count(*) as count from servicerequest
    where To_Char(CREATEDATE,'YYYY') = 2018
    and status = 50
    group by servicecode

        union

    select 1, Count(*) as count from CERTREQUEST
     WHERE to_char(REQDATE,'yyyy') = 2018
    and service = 1

        union

    select 2, Count(*) as count from LTR
     WHERE to_char(REQDATE,'yyyy') = 2018
    and Service = 1
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement