将涉及的总和合并2个查询 [英] Combine 2 queries with sum involved

问题描述

我有2个类似的查询，都看起来像这样，只是在第二个查询中将表从治疗"切换为患者":

I have 2 similar queries that both look like this with only the table being switched from Treatment to Patient in the second query:

SELECT Treatment.Phys_ID AS Phys_ID, Physician.FName, Physician.LName, Sum(Treatment.Charge) AS TotCharge 
FROM Physician 
INNER JOIN Treatment ON Physician.Phys_ID = Treatment.Phys_ID
GROUP BY Treatment.Phys_ID, Physician.FName, Physician.LName;

两者的输出是:

Phys_ID___FName___LName____TotCharge

结合使用后，我需要添加TotCharge的2个查询列以获取实际的TotCharge.但是，当我对所有这两个查询进行UNION时，这些表只是堆叠在彼此之上，而UNION只是重新排列两个表，因此相同的Phys_ID彼此相邻.如何使2个查询的TotCharges加起来?

When combined, I need to add the 2 queries' columns of TotCharge to get the actual TotCharge. However, when I UNION ALL these 2 queries the tables just stack on top of each other and UNION just rearanges both tables so identical Phys_IDs are next to each other. How can I make the 2 queries' TotCharges add up?

推荐答案

您可以使用子查询在医师级别添加费用:

You can use subqueries to add the charges at the Physician level:

SELECT Physician.Phys_ID AS Phys_ID, Physician.FName, Physician.LName, 

  (SELECT Nz(Sum(Treatment.Charge)) 
   FROM Treatment WHERE Treatment.Phys_ID = Physician.Phys_ID) +

  (SELECT Nz(Sum(Patient.Charge))
   FROM Patient WHERE Patient.Phys_ID = Physician.Phys_ID) As Total Charge

FROM Physician;

或者，您可以使用DSum(严格来说是MS Access函数，而不是ANSI SQL).

Alternatively, you can use DSum (strictly an MS Access function and not ANSI SQL).

SELECT Physician.Phys_ID AS Phys_ID, Physician.FName, Physician.LName, 

   Nz(DSum("Charge", "Treatment", "Phys_ID =" &  Physician.Phys_ID)) +

   Nz(DSum("Charge", "Patient", "Phys_ID =" & Physician.Phys_ID)) As Total Charge

FROM Physician;

这篇关于将涉及的总和合并2个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！