CODEWITHSUNDEEP
sql-servert-sql
neel desai
neel desai

Reputation: 43

get sum in query with two tables

i need sum of Amount from TrasactionPaymentsDetails

    DECLARE @mydate DATETIME
SELECT @mydate = '2012-07-28'

select sum(rd.Amount) as total,rp.TransactionCode, rp.ReferenceDate,rp.TransactionFlag,rd.Amount

 from ReceiptsPayments rp,ReceiptsPaymentsDetail rd where 
ReferenceDate >= DATEADD(dd,1-day(@mydate),@mydate)
and ReferenceDate <= DATEADD(dd,0-day(DATEADD(mm,1,@mydate)),DATEADD(mm,1,@mydate))
and TransactionFlag='P';

following is the error. please help

Msg 8120, Level 16, State 1, Line 6 Column 'ReceiptsPaymentsDetail.TransactionCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 1

Views: 77

Answers (3)

Devart
Devart

Reputation: 122040

Try this one -

DECLARE @mydate DATETIME
SELECT @mydate = '20120728'

SELECT  
      total = SUM(rd.Amount) 
    , rp.TransactionCode
    , rp.ReferenceDate
    , rp.TransactionFlag
FROM dbo.ReceiptsPayments rp
JOIN dbo.ReceiptsPaymentsDetail rd ON ON rp.ID = rd.ReceiptPaymentsID
WHERE ReferenceDate >= DATEADD(dd, 1 - DAY(@mydate), @mydate)
    AND ReferenceDate <= DATEADD(dd, - DAY(DATEADD(mm, 1, @mydate)), DATEADD(mm, 1, @mydate))
    AND TransactionFlag = 'P'
GROUP BY 
      rp.TransactionCode
    , rp.ReferenceDate
    , rp.TransactionFlag

Upvotes: 1

xlecoustillier
xlecoustillier

Reputation: 16361

You need to GROUP BY :

DECLARE @mydate DATETIME

SELECT @mydate = '2012-07-28'

SELECT Sum(rd.amount) AS total,
       rp.transactioncode,
       rp.referencedate,
       rp.transactionflag
FROM   receiptspayments rp,
       INNER JOIN receiptspaymentsdetail rd ON rp.id=rd.receiptpaymentsid
WHERE  referencedate >= Dateadd(dd, 1 - Day(@mydate), @mydate)
       AND referencedate <= Dateadd(dd, 0 - Day(Dateadd(mm, 1, @mydate)),
                            Dateadd(mm, 1, @mydate))
       AND transactionflag = 'P'
GROUP  BY rp.transactioncode,
          rp.referencedate,
          rp.transactionflag;

Besides, you'll need to link your tables in order to avoid making a CROSS JOIN, which I suppose is not what you want to get. I assumed there's an Id somewhere in your receiptpayments table, with a FK in receiptpaymentsdetails, hence the INNER JOIN.

Upvotes: 3

Milen
Milen

Reputation: 8877

Add Group By clause to your Statement after the last where condition:

Group By rp.TransactionCode, rp.ReferenceDate,rp.TransactionFlag

Upvotes: 0

Related Questions