sql - 按客户分组并在 SQL 中计算总和并返回前 15 名

Question

我希望按客户分组并汇总 CuryDocBal 并在分组后返回前 15 位客户我当前的代码仅返回 15 行。例如，我希望将 Morton Salt Company 的总数相加并代表 1 行。

   select top 15 a.Name ,d.CuryDocBal
   from
   ARDoc d 
   inner join 
   [AR_Balances] b 
    on d.CpnyID = b.CpnyID
  inner join [SIVSYS].[dbo].[Company] c
  on b.CpnyID = c.CpnyID
  inner join Customer a
  on d.CustId = a.Custid
  
  --DocType = 'IN'
   where 
 d.dueDate BETWEEN dateadd(day, -21, cast(getdate() as date)) and dateadd(day, 7, cast(getdate() as date))

  and CuryDocBal <> 0
 
 group  by a.Name ,
   d.CuryDocBal
   order by   d.CuryDocBal desc;

标签： sqlsql-servertsql

Answer 1

你需要总和（）

    select top 15 a.Name, d.DueDate, sum(d.CuryDocBal) curyDocBal ,d.RefNbr, d.BatNbr,
       b.CpnyID
         ,c.CpnyName
         from 
       ARDoc d 
       inner join 
       [AR_Balances] b 
        on d.CpnyID = b.CpnyID
      inner join [SIVSYS].[dbo].[Company] c
      on b.CpnyID = c.CpnyID
      inner join Customer a
      on d.CustId = a.Custid
      
      --DocType = 'IN'
       where 
     d.dueDate BETWEEN dateadd(day, -21, cast(getdate() as date)) and dateadd(day, 7, cast(getdate() as date))
    --  and d.dueDate < dateadd(day, 7, cast(getdate() as date))
     --  (DueDate BETWEEN GETDATE() AND DATEADD(DAY, +7, GETDATE()) 
        --or
        --DueDate BETWEEN DATEADD(day,-21, GETDATE()) AND  GETDATE())
      and CuryDocBal <> 0
     
     group  by a.Name, d.DueDate,
       d.CuryDocBal, b.CpnyID
         ,c.CpnyName, d.RefNbr, d.BatNbr
       order by   d.CuryDocBal desc;