CODEWITHSUNDEEP
sql-servercountsumaggregateunion
YelizavetaYR
YelizavetaYR

Reputation: 1701

SQL Union Count to Sum Data

I have a bit of a complex query .... I need to do an update statement on the summation of two union-ed SQL queries (problem is the data in the queries isn't numeric so i'm counting rows instead of summing values) but I then need to sum those rows. 

UPDATE  #LT_Actuals_TEMP 
SET     pCount =  h.countPerfs
FROM    (
select count(distinct c.perf_description) as countPerfs, b.program, b.Prog_id
from #LT_Actuals_TEMP TableP
where       a.Performances = 'Y' and a.current_inactive = 0
group by    b.Program, b.Prog_id
union 
select  distinct count(p.perf_code) as countPerfs, x.value, b.Prog_id
from    T_PERF p    
where   x.content_type = 23
group by  x.value, b.Prog_id
) h where h.Prog_id = #LT_Actuals_TEMP.program_id

the first query data comes back as such

countPerfs   program    Prog_id
7            Name       31

and second query comes back as 

countPerfs   program    Prog_id
1            Name       31

what I need pCount to be set to at the end of the day is 8

Expected results
when I do select * from #LT_Actuals_TEMP I see the value 8 for the Program Name, Id 31

Upvotes: 0

Views: 1647

Answers (1)

jpw
jpw

Reputation: 44881

You can solve it by adding another level in the from part where you sum up the data returned from the union.

Your query seems to be missing some source tables (as there are aliases used that don't point to anything) so I guess you're removed some parts, but in general it should look something like this:

UPDATE  #LT_Actuals_TEMP 
SET     pCount =  h.sum_of_countperfs    
FROM    (
  select program, prog_id, sum(countPerfs) as sum_of_countperfs
  from (
    select count(distinct c.perf_description) as countPerfs, b.program, b.Prog_id
    from #LT_Actuals_TEMP TableP
    where       a.Performances = 'Y' and a.current_inactive = 0
    group by    b.Program, b.Prog_id
    union all
    select  distinct count(p.perf_code) as countPerfs, x.value, b.Prog_id
    from    T_PERF p    
    where   x.content_type = 23
    group by  x.value, b.Prog_id
  ) as sub_q group by program, prog_id
) h where h.Prog_id = #LT_Actuals_TEMP.program_id

Also, you probably want to use union all so that duplicates are not removed.

Upvotes: 1

Related Questions