sql求和

Question

我有一个查询结果表格结果表，我想将账单金额相加，以便它返回一行，其中包含一个不同的帐户、余额、总账单金额fPreviousReading、fCurrentReading、fConsumption。

结果应该是

1.account 11074 
2.balance269.49
3.sumbilledamount 520.48
4. fPreviousReading 574 
5 fCurrentReading 612 
6 fConsumption 38

谢谢

查询

select 
    Ten.Account,
    ten.DCBalance AS Balance,   
    SUM(T.fInclusiveAmount)AS BilledAmount,  
    MRD.fPreviousReading,    
    MRD.fCurrentReading , 
    MRD.fConsumption ,  
    T.cDescription 
    from _mtblTransactions T 
    left join _mtblProperties P ON P.idProperty = T.iPropertyID
    left join _mtblPropertyPortions PP ON PP.idPropertyPortions = T.iPortionID
    left join _mtblPropertyPortionServices PPS ON PPS.idPropertyPortionServices = T.iPropertyPortionServiceID
    left join _mtblCategories Cat ON Cat.idCategory = PP.iPortionUsageID        
    left join _mtblServices S ON PPS.iPortionServiceID = S.idService        
    left join _mtblServiceGroups SG ON S.iServiceGroupID = SG.idServiceGroup        
    left join _mtblRateTariffs RT ON RT.idRateTariffs = PPS.iServiceRateTariffID        
    left join Client Ten ON T.iCustomerID = Ten.DCLink  
    left join _mtblMeters M ON PPS.iPropertyPortionMeterID = M.idMeter      
    left join _mtblWalkDetails WD ON WD.iWalkMeterID = PPS.iPropertyPortionMeterID     
    left join _mtblWalks W ON WD.iWalkID = W.idWalk    
    left join Client Own ON P.iPropertyOwnerID = Own.DCLink
    left outer join _mtblRegions R on R.idRegions = P.iPropertyRegionID   
    left outer join _mtblSubRegions SR on SR.idSubRegions = P.iPropertySubRegionID   
    left outer join _mtblAreas A on A.idAreas = P.iPropertyAreaID   
    left join _etblPeriod PER ON T.iPeriodID = PER.idPeriod    
    left join _mtblMeterReadingDetails MRD ON T.iMeterID = MRD.iMeterReadingsMeterID and T.iPeriodID = MRD.iBillingPeriodID 
    and MRD.iReadingType=0  

    Where 
    oWN.Account='11074'
    and idPeriod='79'
        GROUP BY Ten.Account,ten.DCBalance,MRD.fPreviousReading,   MRD.fCurrentReading,   MRD.fConsumption,   T.cDescription 

Answer 1

由于我不知道您的数据，所以有可能我编写了一段代码来返回一些双行。但这是一个你很容易处理的问题。

不过，试一试：

SELECT t1.Account,
       t1.Balance,
       t1.BilledAmount,
       t1.fPreviousReading,
       t1.fCurrentReading,
       t1.fConsumption,
       t2.cDescription
  FROM (SELECT Ten.Account,
               ten.DCBalance AS Balance,
               SUM (T.fInclusiveAmount) AS BilledAmount,
               SUM (MRD.fPreviousReading) AS fPreviousReading,
               SUM (MRD.fCurrentReading) AS fCurrentReading,
               SUM (MRD.fConsumption) AS fConsumption
          FROM      _mtblTransactions T
            left join _mtblProperties P ON P.idProperty = T.iPropertyID
            left join Client Ten ON T.iCustomerID = Ten.DCLink
            left join Client Own ON P.iPropertyOwnerID = Own.DCLink AND oWN.Account='11074'
            left join _etblPeriod PER ON T.iPeriodID = PER.idPeriod
            left join _mtblMeterReadingDetails MRD ON T.iMeterID = MRD.iMeterReadingsMeterID and T.iPeriodID = MRD.iBillingPeriodID
            and MRD.iReadingType=0
            and idPeriod='79'
                GROUP BY Ten.Account,ten.DCBalance) t1
  JOIN (SELECT T.cDescription,
               Ten.Account,
               ten.DCBalance
          FROM      _mtblTransactions T
            left join Client Ten ON T.iCustomerID = Ten.DCLink) t2 ON t2.Account = t1.Account AND t2.DCBalance = t1.DCBalance