时间戳

首页 > 解决方案 > 1 个表组中的列与另一个表中的 2 个其他列的总和

sql - 1 个表组中的列与另一个表中的 2 个其他列的总和

问题描述

我有两张桌子，第一张是

结果

ssn,date,scode,dcode,ppay,ipay
302609074,20180215,4002,1001,25,35
302609074,20180215,4006,1003,40,100
302609074,20181010,4002,1001,25,35
302609074,20181010,4006,1003,40,100
302609074,20190505,4002,1001,25,35
302609074,20190506,4006,1003,40,100
997600970,20190115,4001,1002,25,50
997600970,20190115,4001,1003,40,30
997600970,20190115,4004,1004,40,50
997600970,20190415,4002,1004,40,95
997600970,20190415,4003,1005,40,100
997600970,20181212,4004,1001,25,47
997600970,20181213,4004,1001,25,47
874136439,20190808,4002,1002,25,50
874136439,20190808,4002,1003,40,60
874136439,20190910,4002,1004,40,95
874136439,20190910,4002,1005,60,90
874136439,20191001,4003,1002,25,50
874136439,20191002,4003,1002,25,50
874136439,20191003,4003,1002,25,55
831287780,20190404,4001,1001,25,0
831287780,20190505,4003,1002,25,40
831287780,20190606,4004,1003,25,100
831287780,20190707,4006,1004,60,100
882861510,20190202,4005,1002,60,60
882861510,20190303,4002,1004,25,100
882861510,20190404,4005,1002,40,100
882861510,20190505,4002,1004,50,100
882861510,20190606,4005,1003,50,0
796235486,20190808,4001,1001,25,25
445139565,20180707,4002,1001,40,35
445139565,20190929,4002,1003,40,70
445139565,20190929,4002,1004,40,95
445139565,20190929,4002,1005,40,120
524246868,20190909,4006,1005,100,100
524246868,20190910,4006,1005,100,100
416806352,20190818,4003,1001,25,30
416806352,20190818,4003,1005,50,100
946883650,20190717,4001,1004,25,100
946883650,20190817,4001,1004,25,100
946883650,20190917,4006,1004,50,132
946883650,20191002,4006,1004,50,132
129141378,20190909,4001,1002,25,50
129141378,20190909,4001,1003,0,100
129141378,20190909,4001,1004,0,125
129141378,20190909,4001,1005,0,150
950022926,20181111,4006,1004,182,0
950022926,20190323,4006,1004,100,82
950022926,20190623,4006,1004,0,182
799023031,20190930,4005,1003,50,100
719301054,20190110,4001,1003,25,75
719301054,20190210,4001,1003,25,75
719301054,20190310,4001,1003,25,75
316517393,20190626,4005,1005,0,0
148694321,20190102,4006,1002,40,72
148694321,20190203,4006,1002,40,72
148694321,20190304,4006,1002,40,72
332124842,20190405,4004,1001,25,47
332124842,20190506,4004,1002,25,80
332124842,20190607,4004,1003,25,107

和诊断


scode,dcode,charge
4001,1001,50
4001,1002,75
4001,1003,100
4001,1004,125
4001,1005,150
4002,1001,60
4002,1002,85
4002,1003,1113
4002,1004,
4002,1005,160
4003,1001,55
4003,1002,80
4003,1003,105
4003,1004,130
4003,1005,155
4004,1001,72
4004,1002,102
4004,1003,1312
204,4
4004,1005,192
4005,1001,90
4005,1002,140
4005,1003,190
4005,1004,240
4005,1005,290
4006,1001,77 4006,1002,112
4006,1003,1147
4006,1003,1147
4
4006,1005,217

我需要在同一日期获得每个 SSN 的 SUM（费用）。例如从

302609074,20180215,4002,1001,25,35
302609074,20180215,4006,1003,40,100

我想从表诊断中得到 sum(charge) = 60 + 147 = 207，日期为 20180215

到目前为止，我尝试过的任何方法都没有奏效。例如，我得到：

ELECT Z.date, Z.ssn, TotalCharges
FROM (  
        (SELECT ssn,date,scode,dcode
         FROM outcome
        ) Z
        JOIN 
        (SELECT scode, dcode, charge, SUM(charge) TotalCharges
         FROM diagnosis
        ) U
        ON (U.scode = Z.scode AND U.dcode = Z.dcode)
     )
     GROUP BY Z.date, Z.ssn;

并得到错误 ORA-00937: not a single-group group function

标签： sqloracle

解决方案

为什么要使用这么多子查询？

SELECT o.date, o.ssn, SUM(d.charge)
FROM outcome o JOIN
     diagnosis d
     ON d.scode = o.scode AND d.dcode = o.dcode
GROUP BY o.date, o.ssn;

如果在子查询中，您的查询也可能有效GROUP BY。

推荐阅读