Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004

    Unanswered: how to ? GROUP BY?

    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.

  2. #2
    Join Date
    Oct 2003

    Thumbs up

    Hi, try this
    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
    Last edited by Madhivanan; 12-07-04 at 06:23.

  3. #3
    Join Date
    Dec 2004
    thank you man
    a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts