how to do a sum product in sql server

[jtammyg]

I need to do a sumproduct in SQL Server
ALL_HOSPITALS table
days percentage days avoidable
1 0.244
2 0.127
30 0.890

The calculation would be
0.244*1+0.127*2+0.890*30

The total of the above has to be divided by the sum of days.

I am planning on having a temp table with the hosp name field and the multiplication field only as follows:


INSERT INTO temp_mul_days(hosp_name_key, mul_days)
select ah.hosp_name_key,(ah.per_total_days_avoidable*ah.days)
from all_hospitals ah
left join rft_hosp_name rhn on rhn.hosp_name_key = ah.hosp_name_key
LEFT JOIN RFT_MED_GROUP MG ON MG.MED_GROUP_KEY=AH.MED_GROUP_KEY
where mg.med_group_type = 1
group by ah.hosp_name_key

I am getting the error

Server: Msg 8120, Level 16, State 1, Line 1
Column 'ah.PER_TOTAL_DAYS_AVOIDABLE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'ah.DAYS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I avoid this?

After this query I was planning on having a second temp table with the sum of the results of the above and then another temp table with the sum of the denominator.

The fourth temp table would be the division between the second and third table.

Anybody has a better idea on how to do this?

Thank you!

Tammy

 

[vongrunt]

How about:

INSERT INTO temp_mul_days(hosp_name_key, mul_days)
select ah.hosp_name_key,[!]SUM[/!](ah.per_total_days_avoidable*ah.days)
...

Abd btw. last LEFT JOIN is bridged with WHERE clause... probably nothing to worry about but anyway...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[jtammyg]

Thank you vongrunt...but the sum that way does not give me the result expected

the sum of the parts etc etc...rule

Thank you anyways!

Tammy

 

[vongrunt]

OK, what is your query supposed to insert into temp_mul_days (exact results please)?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLSister]

willthis work for you?

INSERT INTO temp_mul_days(hosp_name_key, mul_days)
select ah.hosp_name_key,(max(ah.per_total_days_avoidable)*max(ah.days))
from all_hospitals ah
left join rft_hosp_name rhn on rhn.hosp_name_key = ah.hosp_name_key
LEFT JOIN RFT_MED_GROUP MG ON MG.MED_GROUP_KEY=AH.MED_GROUP_KEY
where mg.med_group_type = 1
group by ah.hosp_name_key

If you only have one value for each of these fields per hosipital_name_key then it should do what you want.


Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jtammyg]

The results of the multiplication only

0.244
0.254
26.7

After that is done I want to do the sum.

Thank you!

Tammy

 

[vongrunt]

In that case, why GROUP BY? Dupes?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[jtammyg]

We need to group by hospital and hospital appears multiple times.

 

[vongrunt]

Then ... try SELECT DISTINCT with original query and no GROUP BY.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]