How do you calculate using sum in SQL Server

Question

I am trying something like this:

select sum(R_Value) ,((5/cast(sum(R_Value) as int)) * 100.000000000) 
from R_Rating 
where R_B_Id = 135

But I keep getting value 0, sum(R_Value) = 6, so I just want a percentage out of (5/sum(R_Value)) * 100.

How can I get this?

I have a rating of 5 so each user has a rating they can make select from 1 to 5, so what i need is a formula that takes in the sum and takes into account how many users have rated and give us a result from 1 to 5 in the end

Something like this may work but i need to round up to one decimal place to get a whole number.

select sum(R_Value), ((count(*)/cast(sum(R_Value) as float)) * 10) 
from R_Rating 
where R_B_Id = 135

What's the expected result? I think you may want to multiply by 5 and divide by 100, but it's not clear for the skimp description. — The Impaler
– The Impaler, Commented Apr 19, 2020 at 14:38
an INT divided by an INT returns an INT ... So for example Select 5/10 returns 0. HOWEVER Select 5/10.0 returns 0.5000 — John Cappelletti
– John Cappelletti, Commented Apr 19, 2020 at 14:40

The Impaler · Accepted Answer · 2020-04-19 14:55:36Z

1

To get the average rating you need to force floating point algebra. For example:

select 1.0 * sum(R_Value) / count(*)
from R_Rating 
where R_B_Id = 135

Then, if your query selects three rows with the values: 1, 4, and 5, then this query will return 3.33 stars as the average. That is:

= 1.0 * (1 + 4 + 5) / 3
= 1.0 * 10 / 3
= 10.0 / 3
= 3.33333333

answered Apr 19, 2020 at 14:55

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-04-19 14:54:54Z

1

I recommend writing this as:

select sum(R_Value) ,
      (500.0 / sum(R_Value))
from R_Rating 
where R_B_Id = 135;

This avoids an integer division.

answered Apr 19, 2020 at 14:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How do you calculate using sum in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related