PDA

View Full Version : Should Be Simple

TylerDurden180
04-15-2009, 07:12 PM
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!

TylerDurden180
04-15-2009, 07:47 PM
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