'Combine results of two queries with the same value into one row

Here is my query:

select fldUserId, count(*) AS TOTAL
from tblWorkHistory
where fldStatus = '1'
group by fldUserId
union
select fldEmpID, count(*) AS TOTAL
from tblQAHistory
where fldStatus = '1'
group by fldEmpID

Output:

fldUserId  TOTAL

16070004    34
19100015    1
19100015    7
191014571   3

I want to combine both rows with '19100015' into one row.

sql-serverdatabase


Solution 1:[1]

An alternative is to group just once on the outside

select
  t.fldUserId,
  count(*) as TOTAL
from (
    select fldUserId
    from tblWorkHistory
    where fldStatus = '1'
    union all
    select fldEmpID
    from tblQAHistory
    where fldStatus = '1'
) t
group by
  t.fldUserId;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface

Related Questions