Summarize several SQL lines according to the year

  • 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
  • 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

  • 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)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply