Summarize several SQL lines according to the year

• abdalah.mehdoini

  Ten Centuries

  Points: 1015

  More actions

  July 19, 2019 at 7:04 am

  #3664425

  Hello everyone,

  I need your help, in fact from my request which returns me results according to the months of the year I must have the lines summed according to the number of account by year, it is to say that I must have only one line per cumulative account on the year and not the debit and credit amounts on several lines per month, please help me,

  here after my code:

  SELECT DISTINCT
  YEAR(period),
    [T0].[AccountNumber] AS [Code du compte], [T1].[FullName] AS [libellé du compte] , 
   sum(case when  [T0].[AccountNumber]=[T1].[AccountNumber] THEN  [T0].[Totals_DebitAmount] END) AS [Total Mouvements Débits de l’année] ,
   sum(case when  [T0].[AccountNumber]=[T1].[AccountNumber]THEN [T0].[Totals_CreditAmount] END) AS [Total Mouvements Crédits de l’année],
    (CASE
      WHEN Totals_SoldAmount > 0 THEN Totals_SoldAmount
    END) AS [Solde débiteur],
    (CASE
      WHEN Totals_SoldAmount < 0 THEN -Totals_SoldAmount
    END) AS [Solde Créditeur]
  FROM (([AccountTotals] T0
   LEFT OUTER JOIN [Account] T1  ON (([T0].[AccountNumber] = [T1].[AccountNumber])))
   LEFT OUTER JOIN [AccountingYear] T2  ON (([T0].[AccountingYearID] = [T2].[ID]))) 
   
  GROUP BY
  YEAR(period),
  [T0].[AccountNumber] , [T1].[FullName],  [T0].[Totals_DebitAmount],[T0].[Totals_CreditAmount],Totals_SoldAmount

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 19, 2019 at 11:07 am

  #3664475

  It's difficult to help you without any table DDL and sample data.  However, if you don't want the debit and credit amounts to appear separately in your result set, don't include them in the GROUP BY clause.

  John

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  July 19, 2019 at 12:38 pm

  #3664498

  John Mitchell-245523 wrote:

  It's difficult to help you without any table DDL and sample data.  However, if you don't want the debit and credit amounts to appear separately in your result set, don't include them in the GROUP BY clause. John

  In other words, please click on the link in my signature line below and read for one way to do this and why it's important.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)