sql - SQL中的联合计算值求和

我有两个选择语句，我想总结一下。两个查询都工作得很好，但我无法对total的输出求和。我试着跟踪这个question但无法用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"

最佳答案

    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

关于sql - SQL中的联合计算值求和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44594771/