[RESOLVED] SQL Select Statement

hi
i need to do a select statement
there r 3 tables
1. Bills which contains records for all the bills generated for a consumer
2. Master which contains the master details of the consumer
3. Receipt details for the receipts which the consumer may have made for a particular bill (there r chances that the consumer may not have made payment for a particular bill)

i have created a select statement as under:

Code:

---

select Bills.CustNo, CM.Name, Bills.IssueDt, Bills.Qty, Bills.LastRead,
                Bills.PReadDate, Bills.Reading, Bills.Reading, Bills.OP_Bal,
                Bills.Op_Dpc, Bills.Assessment, Bills.Rent ,
                sum(R.ReceiptAmt) as ReceiptAmt
                From Bills
                INNER JOIN Master CM ON Bills.CustNo = CM.CustNo
                LEFT OUTER JOIN Receipt R ON R.CustNo = Bills.CustNo AND r.IssueDate = Bills.IssueDt
                ORDER BY Bills.CustNo, Bills.IssueDt

---

it is giving me an error as follows:

Code:

---

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

---

can someone pls guide me as to what the prob is...now there could b instances where the receipt is not there for that particular custno. for that particular issuedt so that should show as 0

pls. guide
thankx Kuldev

Thread moved to the 'Database Development' forum - which is where you should always post SQL statement questions (while SQL statements can be used in VB, they are certainly not specific to VB)


If you use any aggregate functions (such as Count/Sum/Max/...) anywhere in the Select clause, for every other item in the Select clause you need to also use an aggregate function, or put the field into a Group By clause instead, eg:

Code:

---

                select Bills.CustNo, CM.Name, Bills.IssueDt, Bills.Qty, Bills.LastRead,
                Bills.PReadDate, Bills.Reading, Bills.Reading, Bills.OP_Bal,
                Bills.Op_Dpc, Bills.Assessment, Bills.Rent ,
                sum(R.ReceiptAmt) as ReceiptAmt
                From Bills
                INNER JOIN Master CM ON Bills.CustNo = CM.CustNo
                LEFT OUTER JOIN Receipt R ON R.CustNo = Bills.CustNo AND r.IssueDate = Bills.IssueDt
                GROUP BY Bills.CustNo, CM.Name, Bills.IssueDt, Bills.Qty, Bills.LastRead, ...
                ORDER BY Bills.CustNo, Bills.IssueDt

---

Quote:

---

can someone pls guide me as to what the prob is...now there could b instances where the receipt is not there for that particular custno. for that particular issuedt so that should show as 0

---

as much as i see .you did not put group by clause .so write group by clause in before order by if you are using aggregate function .

Code:

---


ON R.CustNo = Bills.CustNo AND r.IssueDate = Bills.IssueDt
group by Bills.CustNo,CM.Name,Bills.IssueDt,...ORDER BY Bills.CustNo, Bills.IssueDt

---

:)

thankx a lot...prob. solved by grouping.