How to sum two aggregated fields in sql
I want to get the followings:
- Counts of Patients
- Count of Claims
- 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.