how to ? GROUP BY?

zorzi011 · #1 (**permalink**) 12-07-04, 04:56

Hi,
I have a problem with the GROUP BY clause in SQL 2000.

I have 3 tables in db:

1. INVESTORS with fields (idinvestor, name, address etc) ,
2. CONTRACTS with fields (idcontract, idinvestor, contnr, date, etc.) and
3. PAYMENTS with fields (idpayment, idcontract, dateofpayment, amount etc.).

I want to make a report of payments and group them by idcontract, like this :

SELECT idpayment, idcontract, dateofpayment, SUM(amount) FROM PAYMENTS GROUP BY idcontract

but I would like to join tables CONTRACTS and INVESTORS also, so I can have number of the CONTRACT (contnr) and name of the INVESTOR (name) in the same report.
I don't know should I use nested query or JOIN command and if anyone has any example...... ?

Thank you in advance.

Madhivanan · #2 (**permalink**) 12-07-04, 06:19

Hi, try this

Code:

SELECT P.idpayment, P.idcontract, P.dateofpayment, 
SUM(P.amount),I.Name, C.Contnr FROM PAYMENTS P, Investors I ,
Contracts C where P.idContract=C.idContract and C.idinvestor=I.idInvestor
GROUP BY P.idpayment, P.idcontract, P.dateofpayment, I.Name, C.Contnr

Madhivanan

zorzi011 · #3 (**permalink**) 12-07-04, 06:51

thank you man
a lot

Internet Services

Web Development

Digital Marketing

Consumer Tech