Web Analytics Made Easy -
StatCounter Should Be Simple - CodingForum

Announcement

Collapse
No announcement yet.

Should Be Simple

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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

    Comment

    Working...
    X