Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Jhb
    Posts
    20

    Unanswered: Grouping (sum) query

    I have the following information in a table

    ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
    275479 20031202 A -17000 215000 0
    275479 20031202 A -2741 215000 0
    275479 20031202 A -259 215000 -42501729

    I need to create a query that totals TRANQTY and arrives at a result as in the following record.

    ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
    275479 20031202 A -20000 215000 -42501729

    and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..

    SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
    FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
    (SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
    FROM CLIENTSHAREDEALS c1
    WHERE (c1.ACCNO = '275479')
    GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
    WHERE (c2.ACCNO = '275479')
    ORDER BY c1.RUNDATE

    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Grouping (sum) query

    Originally posted by Odie
    I have the following information in a table

    ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
    275479 20031202 A -17000 215000 0
    275479 20031202 A -2741 215000 0
    275479 20031202 A -259 215000 -42501729

    I need to create a query that totals TRANQTY and arrives at a result as in the following record.

    ACCNO Rundate TRDCAP TRANQTY DLPRCE NOTEAMNT
    275479 20031202 A -20000 215000 -42501729

    and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..

    SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
    FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
    (SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
    FROM CLIENTSHAREDEALS c1
    WHERE (c1.ACCNO = '275479')
    GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
    WHERE (c2.ACCNO = '275479')
    ORDER BY c1.RUNDATE

    Thanks
    What about this?

    SELECT ACCNO, Rundate, TrdCap, sum(TRANQTY),DLPRCE, sum(NOTEAMNT) FROM CLIENTSHAREDEALS
    group by ACCNO, Rundate, TrdCap,DLPRCE

  3. #3
    Join Date
    Aug 2003
    Location
    Jhb
    Posts
    20
    Thanks - I'll give it a shot!

Posting Permissions

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