如何在两个不同的表中使用SUM和INNER JOIN

时间：2021-03-05 15:20:36

I am stuck on a query where i have to show list of manufacturers and the Amount of Loan they took and Amount of Refund they returned. i have generated the query but i want to sum total no: of refunds of a manufacturer and total no: of Loans manufacturer has taken. and the difference , that is LoanTaken - RefundAmount = Remaining Amount. If its possible in the query.

我被困在一个查询中,我必须显示制造商列表和他们采取的贷款金额以及他们退回的退款金额。我已经生成了查询,但我想总计没有:制造商的退款和贷款制造商已经采取的总数:。和差异,即LoanTaken - RefundAmount =剩余金额。如果它可能在查询中。

SELECT MM.*, ML.*, MR.*  FROM microfinance_manufacturers AS MM 
                INNER JOIN manufacturer_loans AS ML ON MM.ManufacturerId = Ml.ManufacturerId 
                INNER JOIN manufacturer_refunds AS MR ON MM.ManufacturerId = MR.manufacturerId
                WHERE 1 = 1

ManufacturerId  FirstName  LastName  Gender  Religion  PhoneNumber  EmailAddress  Notes              CustomerAddedDateTime  manufacturerTypeId  manufacturerRoles                                                                                                                                                                                                                                                                                                                                                                                 LoanID  ManufacturerId  LoanAmount  LoanDate    RefundId  manufacturerId  RefundAmount  RefundDate  

         5  Saud       Jibran         0         0  8475983748G  HFDKJFH       VGHJXGVHJD         2015-04-29 14:12:20                    21  O:16:"clsEmployeeRoles":68:{s:56:"aEmployeeRole_Organization_RegionalHierarchy_RegionTypes";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:52:"aEmployeeRole_Organization_RegionalHierarchy_Regions";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:69:"aEmployeeRole_Organization_RegionalHierarchy_Regions_RegionStatistics";a:2:{i:0;i:0;i:2;i:0;}s:82:"aEmployeeRole_Organization_RegionalHierarchy_Regions_     6               5  65644343:1:"2015-05-06Em       6               5  77744s_Station2015-05-06:{
         5  Saud       Jibran         0         0  8475983748G  HFDKJFH       VGHJXGVHJD         2015-04-29 14:12:20                    21  O:16:"clsEmployeeRoles":68:{s:56:"aEmployeeRole_Organization_RegionalHierarchy_RegionTypes";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:52:"aEmployeeRole_Organization_RegionalHierarchy_Regions";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:69:"aEmployeeRole_Organization_RegionalHierarchy_Regions_RegionStatistics";a:2:{i:0;i:0;i:2;i:0;}s:82:"aEmployeeRole_Organization_RegionalHierarchy_Regions_    13               5  543543;s:1:"2015-05-07Em       6               5  77744s_Station2015-05-06:{
         7  Naveed     Ahmed          0         0  847893       hfkjhfskj     fjksddshkfjdshfkj  2015-04-29 14:22:16                    19  O:16:"clsEmployeeRoles":68:{s:56:"aEmployeeRole_Organization_RegionalHierarchy_RegionTypes";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:52:"aEmployeeRole_Organization_RegionalHierarchy_Regions";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:69:"aEmployeeRole_Organization_RegionalHierarchy_Regions_RegionStatistics";a:2:{i:0;i:0;i:2;i:0;}s:82:"aEmployeeRole_Organization_RegionalHierarchy_Regions_    16               7  8798u656:1:"2015-05-07Em       9               7  4354334Station2015-05-07:{
         7  Naveed     Ahmed          0         0  847893       hfkjhfskj     fjksddshkfjdshfkj  2015-04-29 14:22:16                    19  O:16:"clsEmployeeRoles":68:{s:56:"aEmployeeRole_Organization_RegionalHierarchy_RegionTypes";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:52:"aEmployeeRole_Organization_RegionalHierarchy_Regions";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:69:"aEmployeeRole_Organization_RegionalHierarchy_Regions_RegionStatistics";a:2:{i:0;i:0;i:2;i:0;}s:82:"aEmployeeRole_Organization_RegionalHierarchy_Regions_    16               7  8798u656:1:"2015-05-07Em      10               7  896789798ation2015-05-07:{
         7  Naveed     Ahmed          0         0  847893       hfkjhfskj     fjksddshkfjdshfkj  2015-04-29 14:22:16                    19  O:16:"clsEmployeeRoles":68:{s:56:"aEmployeeRole_Organization_RegionalHierarchy_RegionTypes";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:52:"aEmployeeRole_Organization_RegionalHierarchy_Regions";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:69:"aEmployeeRole_Organization_RegionalHierarchy_Regions_RegionStatistics";a:2:{i:0;i:0;i:2;i:0;}s:82:"aEmployeeRole_Organization_RegionalHierarchy_Regions_    17               7  87987687:1:"2015-05-07Em       9               7  4354334Station2015-05-07:{
         7  Naveed     Ahmed          0         0  847893       hfkjhfskj     fjksddshkfjdshfkj  2015-04-29 14:22:16                    19  O:16:"clsEmployeeRoles":68:{s:56:"aEmployeeRole_Organization_RegionalHierarchy_RegionTypes";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:52:"aEmployeeRole_Organization_RegionalHierarchy_Regions";a:4:{i:0;i:0;i:1;i:0;i:2;i:0;i:3;i:0;}s:69:"aEmployeeRole_Organization_RegionalHierarchy_Regions_RegionStatistics";a:2:{i:0;i:0;i:2;i:0;}s:82:"aEmployeeRole_Organization_RegionalHierarchy_Regions_    17               7  87987687:1:"2015-05-07Em      10               7  896789798ation2015-05-07:{

I have attached the pic of the output i am getting... tables are manufacturer,loan,refund, manufacutrerId is common in all tables. now in the pic it shows repeated record as the manufacturer took loan 2 times but return amount 1 time.. but it repeats in refund column. Please Help !!!

我附加了我得到的输出的图片...表是制造商,贷款,退款,manufacutrerId在所有表中很常见。现在在图片中显示重复记录,因为制造商贷款2次但返还金额1次..但它在退款栏中重复。请帮忙 !!!

2 个解决方案

#1

This query should work:

此查询应该有效:

SELECT ManufacturerId,FirstName,LA,RA, LA - RA as RemainingAmount FROM  (SELECT MM.ManufacturerId,MM. FirstName,SUM(LoanAmount) as LA, SUM(RefundAmount) as RA  FROM microfinance_manufacturers AS MM 
                INNER JOIN manufacturer_loans AS ML ON MM.ManufacturerId = Ml.ManufacturerId 
                INNER JOIN manufacturer_refunds AS MR ON MM.ManufacturerId = MR.manufacturerId
                GROUP BY MM.ManufacturerId) as a

#2

You need to do the aggregations before the join. Otherwise, you end up with a cartesian product that throws off the values:

您需要在加入之前执行聚合。否则,你最终得到的笛卡尔积可以抛出这些值:

SELECT MM.*, ML.*, MR.* 
FROM microfinance_manufacturers MM left join
     (select Ml.ManufacturerId, count(*) as numloans, sum(ml.amount) as loanamount
      from manufacturer_loans ML
      group by ManufacturerId
     ) ml
     on MM.ManufacturerId = Ml.ManufacturerId left join
     (select Mr.ManufacturerId, count(*) as numrefunds, sum(ml.amount) as refundamount
      from manufacturer_refunds mr
      group by ManufacturerId
     ) mr
     on MM.ManufacturerId = MR.manufacturerId;

Your question is unclear both on the data layout and on the expected results. But this should be basically what you need.

您的问题在数据布局和预期结果上都不清楚。但这基本上应该是你需要的。

#1

This query should work:

此查询应该有效:

SELECT ManufacturerId,FirstName,LA,RA, LA - RA as RemainingAmount FROM  (SELECT MM.ManufacturerId,MM. FirstName,SUM(LoanAmount) as LA, SUM(RefundAmount) as RA  FROM microfinance_manufacturers AS MM 
                INNER JOIN manufacturer_loans AS ML ON MM.ManufacturerId = Ml.ManufacturerId 
                INNER JOIN manufacturer_refunds AS MR ON MM.ManufacturerId = MR.manufacturerId
                GROUP BY MM.ManufacturerId) as a

#2

You need to do the aggregations before the join. Otherwise, you end up with a cartesian product that throws off the values:

您需要在加入之前执行聚合。否则,你最终得到的笛卡尔积可以抛出这些值:

SELECT MM.*, ML.*, MR.* 
FROM microfinance_manufacturers MM left join
     (select Ml.ManufacturerId, count(*) as numloans, sum(ml.amount) as loanamount
      from manufacturer_loans ML
      group by ManufacturerId
     ) ml
     on MM.ManufacturerId = Ml.ManufacturerId left join
     (select Mr.ManufacturerId, count(*) as numrefunds, sum(ml.amount) as refundamount
      from manufacturer_refunds mr
      group by ManufacturerId
     ) mr
     on MM.ManufacturerId = MR.manufacturerId;

Your question is unclear both on the data layout and on the expected results. But this should be basically what you need.

您的问题在数据布局和预期结果上都不清楚。但这基本上应该是你需要的。

标签：

相关文章