Using results from a temporary column to calculate next column 1

[NikkieG]

I am trying to get SQL to help me with age standardisation and confidence intervals, by 5 year age bands. I can get the data in to 5 year age bands, but as I use the count(*) function to get the number of cases I am finding I can't manipulate this column further.

From the cases I need to divide by a population (fixed number which isn't in a table and will just be inputted into query) to get a rate and then multiply this rate by 100,000 (to get the rate per 100000!) but I keep getting NULL in my rate column!

Basically I need:

Age band Cases Rate Rate/100000
0-4 10 0.00236 236.23
5-9
etc.

in a table

So far I have:

select case when age between 0 and 4 then '0-4'
etc.
end as age_band,
count(*) episodes,
case when ('age_band' = '0-4') then (('episodes'*4427)/100000)
end as rate
from etc...

I need to be able to see which age band the count is for and would ideally like them as rows rather than columns which is why I've used a count rather than

sum(case when age between 0 and 4 then 1 else 0 end) as '0-4'

I haven't been using SQL Server long so am willing to take any advice/help that's out there!

Many thanks!

 

[tlbroadbent]

SQL doesn't allow references to aliases such as age_band or episodes in the select list. You can repeat the function call or use a subquery.

If the Population is the same number for each Age_Band then the following should work.

Select
Age_Band, Episodes,
Episodes/4427. As Rate,
Episodes/4427.*100000. As RatePer100000
From
(Select
case
when age between 0 and 4 then '0-4'
when age between 5 and 9 then '5-9'
...
end as age_band,
count(*) As Episodes
From table_name
Group By
case
when age between 0 and 4 then '0-4'
when age between 5 and 9 then '5-9'
...
end) qry

If the population value differs by Age_Band then you'll need the Case Function as in your example.

Select
Age_Band, Episodes,
Rate = Episodes /
Case
When Age_Band='0-4' Then 4427.
When Age_Band='5-9' Then 4612.
...
End,
RatePer100000= 100000.*Episodes /
Case
When Age_Band='0-4' Then 4427.
When Age_Band='5-9' Then 4612.
...
End
From
(Select
case
when age between 0 and 4 then '0-4'
when age between 5 and 9 then '5-9'
...
end as age_band,
count(*) As Episodes
From table_name
Group By
case
when age between 0 and 4 then '0-4'
when age between 5 and 9 then '5-9'
...
end) qry Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[NikkieG]

Thanks - you're a star - the populations do change, but that works perfectly!

Could I push my luck and pick your brain to find out if you know of a way to get SQL to return all age bands, even if there are some with no cases?

 

[tlbroadbent]

Sure. Create a temp table. Insert all the age_bands into it. Then use the temp table in a left join with the other query.

Create table #age(Age_band varchar(6))
Insert #age values ('0-4')
Insert #age values ('5-9')
Insert #age values ('10-14')
.
.
.
Insert #age values ('95-99')

Select
t.Age_Band, Isnull(Episodes, 0),
Rate = IsNull(Episodes,0) /
Case
When t.Age_Band='0-4' Then 4427.
When t.Age_Band='5-9' Then 4612.
...
End,
RatePer100000= 100000.*Isnull(Episodes,0) /
Case
When t.Age_Band='0-4' Then 4427.
When t.Age_Band='5-9' Then 4612.
...
End
From #age t
Left Join
(Select
case
when age between 0 and 4 then '0-4'
when age between 5 and 9 then '5-9'
...
end as age_band,
count(*) As Episodes
From table_name
Group By
case
when age between 0 and 4 then '0-4'
when age between 5 and 9 then '5-9'
...
end) qry
On t.Age_band=qry.Age_Band Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.