Help with calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with calculation

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-24 : 09:41:27

Have been struggling with this all day, hopefully someone can help.

calculation:
The calculation should summarize (sum(u10_sum) * sum(a10_sum)) / count(id) for every unique id in #table_S
and then add the same calculation for #table_B to the sum.

(sum(u10_sum) * sum(a10_sum)) / count(#table_S.id) + (sum(u20_sum) * sum(a20_sum)) / (#table_N.id)

create table #table_S(
id varchar(10),
group10_id int,
u10_sum float,
a10_sum int
)

create table #table_N(
id varchar(10),
group20_id int,
u20_sum float,
a20_sum int
)

insert into #table_S(id, group10_id, u10_sum, a10_sum) values('1020', 1, 1.7, 3)
insert into #table_S(id, group10_id, u10_sum, a10_sum) values('1021', 1, 2.9, 5)
insert into #table_S(id, group10_id, u10_sum, a10_sum) values('1022', 1, 3.6, 2)
insert into #table_S(id, group10_id, u10_sum, a10_sum) values('1020', 2, 2.0, 6)
insert into #table_S(id, group10_id, u10_sum, a10_sum) values('1021', 2, 0.4, 7)

insert into #table_N(id, group20_id, u20_sum, a20_sum) values('1021', 1, 3.1, 3)
insert into #table_N(id, group20_id, u20_sum, a20_sum) values('1021', 2, 3.8, 5)
insert into #table_N(id, group20_id, u20_sum, a20_sum) values('1022', 1, 1.2, 2)
insert into #table_N(id, group20_id, u20_sum, a20_sum) values('1022', 2, 2.2, 6)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-24 : 10:38:51

Here's one way:


select coalesce(ds.id, dn.id) id
       ,coalesce(ds.calc, 0) + coalesce(dn.calc, 0) calc
from   (
       select id
              ,(sum(u10_sum) * sum(a10_sum)) / count(id) calc
       from   #table_S
       group by id
       ) ds
full outer join
       (
       select id
              ,(sum(u20_sum) * sum(a20_sum)) / count(id) calc
       from   #table_N
       group by id
       )dn
              on dn.id = ds.id

output:
id         calc
---------- ----------------------
1020       16.65
1021       47.4
1022       20.8

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 11:01:49

union all?

select id, sum(calc) as calc
from (
select id
              ,(sum(u10_sum) * sum(a10_sum)) / count(id) calc
       from   #table_S
       group by id

union all

       select id
              ,(sum(u20_sum) * sum(a20_sum)) / count(id) calc
       from   #table_N
       group by id
) as d 
group by id

E 12°55'05.63"
N 56°04'39.26"

maevr
Posting Yak Master

169 Posts

Posted - 2008-09-25 : 02:00:24

Thanks for your help, I will use the union all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources