Use Aggregate Values

[jannat]

Hi all,

I have a table with the following fields:

carrier,calc_date,ind_id,rcf

I need to run a query to get the following result(by carrier and for each calc_date, calculate the percentage of all individuals who have rcf greater than 0.73):

carrier,calc_date,count of ind with rcf > 0.73, count of all individual, percentage of individuals with rcf's greater than 0.73.

does anyone have an idea of how to achieve that result?

Thanx

[dim]

Here is the way of how you can do that (if I understood you right):

--St.1
create table #tempt (carrier varchar(10),calc_date datetime,ind_id int, rcf decimal(5,2))

--St.2 insert different data to test the result using the script below bu changing the hardcoded values:

insert into #tempt (carrier,calc_date,ind_id,rcf)
values('DHL', '2003-06-22', 13, 0.5)

--St.3 In this select statement the RCF_FLG is 1 if rcf is more than 0.73 and 0 if not, and percentage of the both group.

select carrier,calc_date,
case when rcf > 0.73 then 1 else 0 end RCF_FLG, count(ind_id)*100.00/(select count(carrier) from #tempt where carrier = carrier and calc_date = calc_date) PERCENTAGE
from #tempt
group by carrier, calc_date, case when rcf > 0.73 then 1 else 0 end

Hope it helps,
Dim

[Claire]

If you just need to express as numeric way

select case when cas/total*100 <> 0 then cas/total*100 else 0 end
from (select cast(count(rcf) as decimal(4,2)) as cas,carrier,calc_date from c where rcf > 0.73 group by carrier,calc_date)a
full outer join (select cast(count(rcf) as decimal(4,2)) as total,carrier,calc_date from c group by carrier,calc_date)B
on a.carrier = b.carrier and a.calc_date = b.calc_date


If you want to express by use of '%'

select left(cast((case when cas/total*100 <> 0 then cas/total*100 else 0 end) as varchar(12)),5)+'%'
from (select cast(count(rcf) as decimal(4,2)) as cas,carrier,calc_date from c where rcf > 0.73 group by carrier,calc_date)a
full outer join (select cast(count(rcf) as decimal(4,2)) as total,carrier,calc_date from c group by carrier,calc_date)B
on a.carrier = b.carrier and a.calc_date = b.calc_date

[jannat]

Thank you very much for your help Claire, just one small problem. It's giving me an arithmetic overflow error, how can I round the decimal points?

[Claire]

I think this will solve your problem
PS.MyTable is the table name!You should change it to the tablename you are working on ,All the columns that I created in the Mytable are the same like you defined:Carrier,rcf,calc_date,ind_id


select carrier,calc_date,case when Percentage <>'0.%' then Percentage else '0%' end as Percentage from
(select b.carrier,b.calc_date,left(cast((case when [case]/total*100 <> 0 then round([case]/total*100,0) else 0 end) as varchar(20)),2)+'%' as Percentage
from (select cast(count(rcf) as decimal(8,2)) as [case],carrier,calc_date from MyTable where rcf > 0.73 group by carrier,calc_date)a
full outer join (select cast(count(rcf) as decimal(8,2)) as total,carrier,calc_date from MyTable group by carrier,calc_date)B
on a.carrier = b.carrier and a.calc_date = b.calc_date)C