根据表的另一个总和对表的一列求和 [英] Sum a column of a table based on another sum of a table

问题描述

我有这个代码:

declare @ReportLines table 
    (RebateInvoiceID int, 
     RebateSetupID int ,
     ShortItemNo float primary key(RebateInvoiceID,RebateSetupID,ShortItemNo),
     TotalAmount float,
     InvoiceTotal float,
     TotalQuantity int )
insert @ReportLines
select
  i.RebateInvoiceID
, coalesce(rs.WholesalerRebateSetupID,r.RebateSetupID)
, bl.ShortItemNo
, sum(round(r.Amount,2)) as TotalAmount
, sum(round(TotalAmount,2)) as InvoiceTotal
, sum(r.Quantity) TotalQuantity
from
  @Invoices i
  join RebateInvoices ri (nolock) on 
    ri.RebateInvoiceID=i.RebateInvoiceID
  inner loop join Rebates r (nolock) on
    r.RebateInvoiceID=i.RebateInvoiceID       
  join RebateSetup rs (nolock) on
    rs.RebateSetupID=r.RebateSetupID
  join BidLines bl (nolock) on 
    r.BidLineGuid=bl.BidLineGuid
  join @Products p on
    p.ShortItemNo=bl.ShortItemNo
  left join ChargebackDetailHistory cd (nolock) on 
    r.DocumentBranchPlant = cd.DocumentBranchPlant
    and r.DocumentNumber = cd.DocumentNumber
    and r.DocumentType = cd.DocumentType
    and r.LineNumber = cd.LineNumber
  left join EDI.dbo.JDE_SaleDetail sd (nolock) on 
    r.DocumentBranchPlant = sd.BranchPlant
    and r.DocumentNumber = sd.OrderNumber
    and r.DocumentType = sd.OrderType
    and r.LineNumber = sd.LineNumber

where 
    cd.InvoiceDate between @BeginDate and @EndDate
    or sd.InvoiceDate between @BeginDate and @EndDate
group by
  i.RebateInvoiceID
, coalesce(rs.WholesalerRebateSetupID,r.RebateSetupID)
, bl.ShortItemNo

我想对总金额列中的发票总金额列求和.当我运行上面的查询时，我得到了一堆奇怪的总数.发票总数应该是每一行的一个数字，并且与 rebateinvoiceid 一致，因为这是它的分组方式，对吗?

I want to sum the invoice total amount column from the total amount column. When I run the above query i get a bunch of weird totals. The invoice total should be one number for each row and be consistent with the rebateinvoiceid since thats what its grouped by, correct?

推荐答案

您可以进行另一个 Nesty 查询...类似这样的事情:

You can make another Nesty query... Something Like this:

insert @ReportLines
select
   RebateInvoiceID
   , ID
   , ShortItemNo
   , TotalAmount
   , sum(round(TotalAmount,2)) as InvoiceTotal
   , TotalQuantity
from
( 
   select
   i.RebateInvoiceID
   , coalesce(rs.WholesalerRebateSetupID,r.RebateSetupID) as ID
   , bl.ShortItemNo
   , sum(round(r.Amount,2)) as TotalAmount
   , sum(r.Quantity) TotalQuantity
  from (... <the rest of your code here> ...) 
) As MyTable
group by
 RebateInvoiceID, ID, ShortItemNo, TotalAmount, TotalQuantity

这篇关于根据表的另一个总和对表的一列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！