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?


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 

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

In snowflake it's even easier, just add them together via the alias you name the output column. Snowflake as see total_count is derived from the two aggregate functions result, so it doesn't need to be added to the GROUP BY clause. rather neat.

SELECT  
    year, 
    COUNT(DISTINCT patientid) AS patitent_count, 
    COUNT(DISTINCT claimid) AS claims_count,
    patitent_count + claims_count AS total_count 
FROM sand_scipher_ds_db.ats.sym
GROUP BY 1
ORDER BY 1;

in other DB's you have write the same SQL 2+ times like in eshirvana answer, or use a sub-query.