Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Should Be Simple

    Hi,

    I have the below code written and it returns the values correctly, however not in the way I would like it. Basically I need the total sum of TBALLOC.SHARES & TBALLOC.COMMISSION for each TICKET.BROKER. I'm fairly new at this so any help would be appreciated.

    Idealy this is how it would look

    Goldman Sachs 5,000 $200
    ITG 10,000 $400
    Aqua 5,000 $200


    Currently using the compute clause, it seperates each transaction by the broker, then under give me the totals, however I would like to eliminate each transation and just have just the brokers and totals. Here is my code....



    SELECT TBALLOC.CLIENT_CODE, TICKET.TICKER, TBALLOC.SHARES,
    TBALLOC.COMMISSION, TICKET.BROKER, TICKET.SECTYPE

    FROM
    cepmftp_dat.dbo.TICKET, cepmftp_dat.dbo.TBALLOC, cepmftp_dat.dbo.SECUMST2

    WHERE
    TBALLOC.TICKNUM = TICKET.TICKNUM
    and TICKET.CONFIRMED = '1'
    and TICKET.SECTYPE = 'CS'


    Group By TBALLOC.CLIENT_CODE, TICKET.TICKER, TBALLOC.SHARES, TBALLOC.COMMISSION, TICKET.BROKER, TICKET.SECTYPE

    Order By TICKET.BROKER, TBALLOC.COMMISSION

    compute sum(TBALLOC.SHARES), sum(TBALLOC.COMMISSION) BY TICKET.BROKER





    Here are the results:

    ---
    MARION CY 600.0 7.2000000000000002 INST CS
    MARION IDTI 1125.0 13.5 INST CS
    CLIND CY 3200.0 38.399999999999999 INST CS
    CLIND IDTI 6500.0 78.0 INST CS
    LATEACH CY 21200.0 254.40000000000001 INST CS
    LATEACH IDTI 43000.0 516.0 INST CS
    ----

    75625.0 907.5

    ---

    CLIND NHP 500.0 7.5 ITGI CS
    LATEACH NHP 3450.0 51.75 ITGI CS

    ---

    3950.0 59.25
    ---


    With this example I would rather have the results as this, because then I'm going to create a .bat file to call this ad hoc:

    ITGI 3950 59.25
    INST 75625.0 907.5

    I appreciate all of your help!

  • #2
    New to the CF scene
    Join Date
    Apr 2009
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured it out, it was really simple...



    SELECT TBALLOC.BROKER,
    SUM(TBALLOC.SHARES),
    SUM(TBALLOC.COMMISSION)

    FROM cepmftp_dat.dbo.TBALLOC, cepmftp_dat.dbo.TICKET

    WHERE
    TBALLOC.TICKNUM = TICKET.TICKNUM
    and TICKET.CONFIRMED = '1'
    and TICKET.SECTYPE = 'CS'

    GROUP BY TBALLOC.BROKER


    Current Results

    INST 89300.0 1071.5999999999999
    ITGI 86601.0 1299.02
    JEAG 4026.0 48.310000000000002
    MSAG 13825.0 165.90000000000001
    STFL 15325.0 613.0


  •  

    Posting Permissions

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