select hosp_name as hosp,
       sum(No_of_study)as Mri,
       (select sum(No_of_study)as Ct from financeOutsourcingDetails where mod_name ='ct' and hosp_name='Ramesh Hospital') as no_of_ct
       sum(tot_amt) as total_amount 
from  financeOutsourcingDetails 
where mod_name ='mri' 
group by Hosp_name

select hosp_name as hosp,
       sum(case when  mod_name ='mri' then No_of_study end) as Mri,
       sum(case when  mod_name ='ct' then No_of_study end) as no_of_ct,
       sum(tot_amt) as total_amount 
from  financeOutsourcingDetails 
group by Hosp_name

quote:

---

Originally posted by visakh16

this maybe?

select hosp_name as hosp,
       sum(case when  mod_name ='mri' then No_of_study end) as Mri,
       sum(case when  mod_name ='ct' then No_of_study end) as no_of_ct,
       sum(tot_amt) as total_amount 
from  financeOutsourcingDetails 
group by Hosp_name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

select hosp_name as hosp,
       sum(case when  mod_name ='mri' then No_of_study end) as Mri,
       sum(case when  mod_name ='ct' and hosp_name='Ramesh Hospital' then No_of_study end) as no_of_ct,
       sum(tot_amt) as total_amount 
from  financeOutsourcingDetails 
group by Hosp_name