Calculate an average age in SQL Reporting Services off an age range

tracimcp
tracimcp used Ask the Experts™
on
I have the following in SQL Reporting services:

Age Range    #    
<65                1
65-69             0
70-74             3
75-79             6
80-84             1
>85              10
Total            21
I need to display the Average Age and I'm not sure to do this.  Please help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this is what you require:

SELECT AGE_RANGE, AVG(CNT) as average_val
FROM ur_table
GROUP BY AGE_RANGE

Kindly note that this will ignore NULL values.

Kindly revert if your requirement differs

Author

Commented:
The requirement is for the average age ex. 74 which is what is confusing
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> which is what is confusing

Kindly specify your exact requirements so that we can try to help you out.

Author

Commented:
II have the following in a matrix report in SQL Reporting Services.  At the bottom of the report it will have total  for the ages and also an average age.    I'm not sure how to do this will the age range.

Age Range    #    
<65                1
65-69             0
70-74             3
75-79             6
80-84             1
>85              10
Total            21
Average  Age  74
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Then I hope that you have the query somewhat like

Hope this helps

select age_range, count(*)
from ur_table
group by age_range
 
-- Just include WITH ROLLUP option here
 
select case when age_range is null then 'Total' else age_range end as age_range, count(*)
from ur_table
group by age_range WITH ROLLUP
union all
select 'Average age', avg(age)
from ur_table

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial