4

I have two SELECT statements that I'd like to sum. Both queries work fine but I'm not able to SUM the output of total. I tried following this question but unable to SUM by wrapping the query in select id, sum(amount) from ( )

SELECT "patient_profiles"."id", count(distinct recommendations.id) AS total
FROM "patient_profiles" 
  LEFT OUTER JOIN 
  "recommendations" ON "recommendations"."patient_profile_id" = "patient_profiles"."id" 
GROUP BY "patient_profiles"."id"

UNION

SELECT "patient_profiles"."id", count(distinct patient_profile_potential_doctors.id) AS total
FROM "patient_profiles" 
  LEFT OUTER JOIN "patient_profile_potential_doctors" ON "patient_profile_potential_doctors"."patient_profile_id" = "patient_profiles"."id"       
GROUP BY "patient_profiles"."id"
zt1983811
  • 1,011
  • 3
  • 14
  • 34
user2954587
  • 4,661
  • 6
  • 43
  • 101
  • You said, "unable to SUM by wrapping the query in `select id, sum(amount) from ( )`" do you get an error? you change amount to total right? – xQbert Jun 16 '17 at 17:39

1 Answers1

9
    Select ID, sum(Total) from  
      (
        SELECT "patient_profiles"."id" [ID], count(distinct recommendations.id) AS total
        FROM "patient_profiles" 
          LEFT OUTER JOIN 
          "recommendations" ON "recommendations"."patient_profile_id" = "patient_profiles"."id" 
        GROUP BY "patient_profiles"."id"

        UNION

        SELECT "patient_profiles"."id" [ID], count(distinct patient_profile_potential_doctors.id) AS total
        FROM "patient_profiles" 
          LEFT OUTER JOIN "patient_profile_potential_doctors" ON "patient_profile_potential_doctors"."patient_profile_id" = "patient_profiles"."id"       
        GROUP BY "patient_profiles"."id"
) x
    group by ID
Herman
  • 300
  • 2
  • 9