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

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.

CodePudding user response：

Use your query as a sub-query:

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

CodePudding user response：

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;