I want to get the followings:

1. Counts of Patients
2. Count of Claims
3. total count (i.e., Patient_Count Claim_Count)

select  year, 
    count(distinct patientid) as Patitent_Count, 
    Count(distinct CLAIMID) as Claims_Count,
    sum(Patitent_Count   Claims_Count) as Total_count 
    from sand_scipher_ds_db.ATS.sym
    group by year
    order by year

Error:

SQL compilation error: Aggregate functions cannot be nested: [COUNT(DISTINCT SYM.PATIENTID)] nested in [SUM(PATITENT_COUNT CLAIMS_COUNT)]*

I've also tried with following subquery:

select x.*,
sum(x.Patitent_Count   x.Number_of_claim) as Total_count
from( 
  select  year, count(distinct patientid) as Patitent_Count, Count(distinct CLAIMID) as Number_of_claim from sand_scipher_ds_db.ATS.sym
  group by year
  order by year)x
  group by year
  order by year

however still getting the same error Can anyone please suggest a way to do this?

CodePudding user response：

You can do that with a subquery:

select  year, Patitent_Count, Claims_Count, Patitent_Count   Claims_Count as Total_Count
from(select  year, 
count(distinct patientid) as Patitent_Count, 
Count(distinct CLAIMID) as Claims_Count
from sand_scipher_ds_db.ATS.sym
group by year) t
order by year

CodePudding user response：

you even don't need subquery :

select
    year,
    count(distinct patientid) Patitent_Count,
    count(distinct CLAIMID) Claims_Count,
    count(distinct patientid)   count(distinct CLAIMID) as Total_Count
from sand_scipher_ds_db.ATS.sym
group by year
order by year