sql - 汇总数据并获取 SUM 组

Question

我有两张 table :

**OrgPhysicianMappingtbl**
org varchar
physician varchar

**PhysicianDatatbl**
physician varchar
names varchar
datapoint int

来自 OrgPhysicianMappingtbl 的数据:

+---------+-----------+
|   org   | physician |
+---------+-----------+
| ABC123  | B032      |
| ABC123  | B023      |
| ABC123  | A022      |
| ABB443  | A32       |
| ABB332  | F23       |
| BVD222  | G23       |
| BVD222  | GG2       |
| BOS5223 | G4        |
+---------+-----------+

来自 PhysicianDatatbl 的数据:

+------------+----------------------------------+-----------+
| physician  |              names               | datapoint |
+------------+----------------------------------+-----------+
| B032       | CASH                             | 68        |
| B032       | MEDICAID                         | 89        |
| B032       | ALL THIRD PARTY                  | 3,769     |
| B032       | WORKERS COMP                     | 39        |
| B032       | US SCRIPT (PROC-UNSP)            | 86        |
| B032       | MEDCO HLTH SOLUTIONS (PROC-UNSP) | 79        |
| B032       | BCBS WELLPOINT/ANTHEM/WEL UNSPEC | 76        |
| B032       | KY EMPLOYEES HLTH PLN/KEHP (KY)  | 62        |
| B032       | UHC/PAC/AARP MED PDP GENERAL(KY) | 52        |
| B032       | WELLCARE OF KENTUCKY (KY)        | 42        |
| B032       | CCRX MED PDP GENERAL (KY)        | 39        |
| B032       | WORKERS COMP - EMPLOYER          | 37        |
| B032       | HUMANA/CAREPLS MED D GENERAL(KY) | 27        |
| B032       | CIGNA MEDICARE RX PDP GNRL (KY)  | 26        |
| B023       | CASH                             | 167       |
| B023       | MEDICAID                         | 34        |
| B023       | ALL THIRD PARTY                  | 3,165     |
| B023       | WORKERS COMP                     | 56        |
| B023       | WORKERS COMP - EMPLOYER          | 50        |
| B023       | BLUE CHOICE PPO (TX)             | 48        |
| B023       | TRICARE HUMANA MILITARY SOUTH    | 47        |
| B023       | MEDCO HLTH SOLUTIONS (PROC-UNSP) | 32        |
| B023       | BROADSPIRE INC                   | 27        |
| B023       | ADVANCEPCS (PROC-UNSP)           | 27        |
| B023       | UNITED HLTHCARE-(TX) TEXAS       | 23        |
| B023       | HEALTHSPRING PDP (TX)            | 18        |
| B023       | AETNA INC.-(TX) HOUSTON          | 13        |
| B023       | WELLCARE MEDICARE D GENERAL(TX)  | 12        |
+------------+----------------------------------+-----------+

预期结果示例:

+-----------+-----------------+-----------+
| org       |      names      | sum(datapoints|
+-----------+-----------------+-----------+
| ABC123    | CASH            | 236       |
| ABC123    | MEDICAID        | 123       |
| ABC123    | ALL THIRD PARTY | 6,933     |
| ABC123    | WORKERS COMP    | 94        |
| …         | …               | …         |
+-----------+-----------------+-----------+

我需要将我的数据汇总到 ORG 级别。每个 ORG 有多名医生。每个医生都有与之关联的 names 和 datapoints。我想要的结果是每个 names 汇总到 org 级别的所有 datapoints 的总和。

有人可以帮我开始吗？

这是我尝试过的:

select o.org,p.names,sum(p.datapoint)
from OrgPhysicianMappingtbl o
join PhysicianDatatbl p
on o.physician=p.physician
group by o.org,p.names

Answer 1

您可以使用 GROUPING SETS :

select o.org, p.names, sum(p.datapoint)
from OrgPhysicianMappingtbl o 
inner join PhysicianDatatbl p
  on o.physician = p.physician
GROUP BY GROUPING SETS((o.org), (p.names))

见 SQL Fiddle with Demo