• The first thing that stands out is why do you have all the NOLOCK hints? You are aware of the problems that this hint causes?

    Second you need to treat each SELECT in a UNION as its own grouping set

    So you would group up the first select, union, group the second select

    SELECT

    Col1,

    Col2,

    FROM

    Tab1

    GROUP BY

    Col1

    UNION

    SELECT

    Col1,

    Col2

    FROM

    Tab2

    GROUP BY

    Col1