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!
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!