Get Sum of BudgetTransactionLine

Question

I'm trying to get sum of budget transaction line by month .Is there an error on my Query. The result of my query is :

RECIDLine   RecIDHeader Date                      Amount
5637157326  5637149076  2012-08-01 00:00:00.000   850.00
5637157342  5637149079  2012-12-01 00:00:00.000   1000.00
5637157343  5637149079  2012-12-01 00:00:00.000   80.00
5637157344  5637149079  2012-12-01 00:00:00.000   2700.00

But i want to get somthing like this :

 RECIDLine  RecIDHeader Date                      Amount
    5637157326  5637149076  2012-08-01 00:00:00.000   850.00
    5637157342  5637149079  2012-12-01 00:00:00.000   3780.00

This is my query :

IF OBJECT_ID('tempdb..#BudgetTransTmp') IS NOT NULL
        DROP TABLE #BudgetTransTmp
    Select  
            BudgetTransactionLine.RECID                                     AS RecIdLine,
            BUDGETTRANSACTIONHEADER.RECID                                   AS RecIdHeader,
            BudgetTransactionLine.DATE,
            SUM(CAST((BudgetTransactionLine.TransactionCurrencyAmount )  as decimal(18,2))) AS Amount
    INTO   #BudgetTransTmp 
    FROM   MicrosoftDynamicsAX.dbo.BudgetTransactionLine                    AS BudgetTransactionLine

          INNER JOIN MicrosoftDynamicsAX.dbo.BUDGETTRANSACTIONHEADER        AS BUDGETTRANSACTIONHEADER
            ON  BUDGETTRANSACTIONHEADER.RECID                               =  BudgetTransactionLine.BUDGETTRANSACTIONHEADER
            AND BUDGETTRANSACTIONHEADER.budgetTransactionType               =  '3'
            AND BUDGETTRANSACTIONHEADER.PARTITION                           =  @Partition

    WHERE   BudgetTransactionLine.PARTITION                                 =@Partition
            AND BudgetTransactionCode.DATAAREAID = 'USMF'  
            AND  BudgetTransactionLine.DATE >= PeriodCalandarTmp.StartDate
            AND  BudgetTransactionLine.DATE <= PeriodCalandarTmp.EndDate


    GROUP BY    BudgetTransactionLine.DATE,
                BUDGETTRANSACTIONHEADER.RECID,
                BudgetTransactionLine.RECID             
    select * from #BudgetTransTmp

And I need to keep BudgetTransactionLine.RECID in select

Answer 1

You should not include BudgetTransactionLine.RecId (RecIdLine) in your GROUP BY.

If you need this column, then you must use one of Aggregate Function (for example in the SELECT part MIN(BudgetTransactionLine.RecId) AS RecIdLine.