Help Needed in Calculating Average

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 9:11 am

  #300408

  Hi,

  Below data is my output of one of the query,

  With Record (Months,RScore,RAmount,Rvisit,TotalAmt) as (

  select 'JAN' as Months, 50 as RScore,20 as RAmount,10 as Rvisit,200 as TotalAmt union all

  select 'FEB',44,13,8,100 union all

  select 'MAR',32,13,8,100 union all

  select 'APR',70,13,8,100 union all

  select 'MAY',80,13,8,100)

  I need the take the average of Rscore and and my output should be like below,

  With Record (Months,RScore,RAmount,Rvisit,TotalAmt,RScoreAVG) as (

  select 'JAN' as month, 50 as RScore,20 as RAmount,10 as Rvisit,200 as TotalAmt,55 as RScoreAVG union all

  select 'FEB',44,13,8,100,55 union all

  select 'MAR',32,13,8,100,55 union all

  select 'APR',70,13,8,100,55 union all

  select 'MAY',80,13,8,100,55)

  Any sample query please how to achieve this. this is little tricky and am confused how to apply aggregate on this.

• imex

  Ten Centuries

  Points: 1234

  More actions

  January 16, 2015 at 9:21 am

  #1770792

  Hi,

  Try something like this:

  With Record (Months,RScore,RAmount,Rvisit,TotalAmt) as (

  select 'JAN' as Months, 50 as RScore,20 as RAmount,10 as Rvisit,200 as TotalAmt union all

  select 'FEB',44,13,8,100 union all

  select 'MAR',32,13,8,100 union all

  select 'APR',70,13,8,100 union all

  select 'MAY',80,13,8,100)

  select *, AVG(RScore) OVER() as RScoreAVG

  from Record

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• roryp 96873

  SSCertifiable

  Points: 6649

  More actions

  January 16, 2015 at 9:22 am

  #1770793

  So you just want the average of all RScores on each line? You can use AVG and partition over nothing to get that:

  With Record (Months,RScore,RAmount,Rvisit,TotalAmt) as (

  select 'JAN' as Months, 50 as RScore,20 as RAmount,10 as Rvisit,200 as TotalAmt union all

  select 'FEB',44,13,8,100 union all

  select 'MAR',32,13,8,100 union all

  select 'APR',70,13,8,100 union all

  select 'MAY',80,13,8,100)

  select *, AVG(RScore) over () RScoreAVG

  from Record;

• Lowell

  SSC Guru

  Points: 323504

  More actions

  January 16, 2015 at 9:23 am

  #1770795

  looks pretty straight forward to me:

  select

  AVG(RSCORE) AS ScoreAvg,

  AVG(RAmount) As AmtAvg,

  AVG(Rvisit) AS VistAvg,

  SUM(TotalAmt) AS TotalAmount

  from Record

  /*--Results

  ScoreAvg AmtAvg VistAvg TotalAmount

  ----------- ----------- ----------- -----------

  55 14 8 600

  */

  With Record (Months,RScore,RAmount,Rvisit,TotalAmt) as (

  select 'JAN' as Months, 50 as RScore,20 as RAmount,10 as Rvisit,200 as TotalAmt union all

  select 'FEB',44,13,8,100 union all

  select 'MAR',32,13,8,100 union all

  select 'APR',70,13,8,100 union all

  select 'MAY',80,13,8,100)

  select AVG(RSCORE) AS ScoreAvg,

  AVG(RAmount) As AmtAvg,

  AVG(Rvisit) AS VistAvg,

  SUM(TotalAmt) AS TotalAmount

  from Record

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• roryp 96873

  SSCertifiable

  Points: 6649

  More actions

  January 16, 2015 at 9:23 am

  #1770796

  D'oh! Too slow. Use the answer imex posted. 😛

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 9:39 am

  #1770805

  thank you guys for all the reply and imex, your solution works for me.

  Hi Lowell,

  thanks for your reply and your query gives aggregate of all the records and plots in single row. My requirement is different.

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 9:54 am

  #1770811

  quick question about avg mathematical,

  i data i gave as sample data. For my original case, the sum i am getting as 423 and total months as 6.

  so 423/6 has to be 70.5 But when i use AVG(RScore), it shows 70 as average. So how can i make it as whole value. if it is 70.4 then we can display 70. but if it is 70.5 and above i need to display 71. please suggest me

• roryp 96873

  SSCertifiable

  Points: 6649

  More actions

  January 16, 2015 at 10:02 am

  #1770814

  KGJ-Dev (1/16/2015)

  ---

  quick question about avg mathematical,

  i data i gave as sample data. For my original case, the sum i am getting as 423 and total months as 6.

  so 423/6 has to be 70.5 But when i use AVG(RScore), it shows 70 as average. So how can i make it as whole value. if it is 70.4 then we can display 70. but if it is 70.5 and above i need to display 71. please suggest me

  The average will keep the same datatype as the values you are taking the average of. In your cte you didn't specify a data type, but SQL Server implicitly converts them to an int, so your average will be an int (no decimal places). Either declare the correct datatype in your cte (or table if that's what you are actually using), or convert them before doing the AVG like this:

  With Record (Months,RScore,RAmount,Rvisit,TotalAmt) as (

  select 'JAN' as Months, 50 as RScore,20 as RAmount,10 as Rvisit,200 as TotalAmt union all

  select 'FEB',44,13,8,100 union all

  select 'MAR',32,13,8,100 union all

  select 'APR',71,13,8,100 union all

  select 'MAY',80,13,8,100)

  select *, AVG(convert(numeric(5,2), RScore)) over () RScoreAVG

  from Record;

  Adjust the scale and precision of the numeric as needed for your case.

  Edit: I sort of misread your question and now see that you want to round. Still the same idea, just wrap the AVG in a round to 0 decimal places:

  round(AVG(convert(numeric(5,2), RScore)) over (), 0)

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 10:22 am

  #1770824

  hi roryp,

  thanks for the reply and i am getting the output as 71.000000. How can i round this as 71. Because i don't want to have the precision.

  how can i remove the trailing zeros. any suggestion please

• roryp 96873

  SSCertifiable

  Points: 6649

  More actions

  January 16, 2015 at 10:24 am

  #1770825

  KGJ-Dev (1/16/2015)

  ---

  hi roryp,

  thanks for the reply and i am getting the output as 71.000000. How can i round this as 71. Because i don't want to have the precision.

  how can i remove the trailing zeros. any suggestion please

  Convert it back to an int after the rounding is done. I may be making this clunkier than it needs to be and someone can get you a little more elegant code, but this will work:

  convert(int, round(AVG(convert(numeric(5,2), RScore)) over (), 0))

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 10:28 am

  #1770828

  even i tried the below code

  CAST(round(AVG(convert(numeric(5,2), RScore)) over (), 0) as decimal(8,0))

  it works. but would it impact anything?

• roryp 96873

  SSCertifiable

  Points: 6649

  More actions

  January 16, 2015 at 10:31 am

  #1770829

  KGJ-Dev (1/16/2015)

  ---

  even i tried the below code

  CAST(round(AVG(convert(numeric(5,2), RScore)) over (), 0) as decimal(8,0))

  it works. but would it impact anything?

  Nope, as long as you have a large enough scale and precision in your converts for the numbers you are dealing with, you should be fine.

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 11:17 am

  #1770844

  thank you dude. appreciate your time on this.

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  January 16, 2015 at 11:19 am

  #1770845

  thank you dude. appreciate your time on this.