Sum Totaling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sum Totaling

Author

Topic

cherman
Starting Member

15 Posts

Posted - 2011-07-18 : 15:55:04

Below is my sql statement. I would like to sum only the t.order_id field. How do I do this?

select ts.acct_cd, t.trans_type, t.order_id, sm.ticker, sm.cusip, t.status, ts.exec_broker, b.bkr_name,
t.exch_cd, ts.commision_amt, ts.create_date, t.trade_date, t.trade_date as 'end', t.settle_date,
ts.commision_base_amt, ts.EXEC_BROKER_REASON, t.instruction, ts.trading_venue, sm.sec_name,
s.sec_typ_cd, sm.sec_id, t.trader, ts.exec_qty, ts.exec_price, ts.exec_amt, ts.net_prin_amt,
(select bk.target_amt from cs_fund_broker bk where ts.exec_broker = bk.acct_cd)as target_amt
from ts_order t, ts_order_alloc ts, ts_order_sec_spec s,
cs_broker b, csm_security sm
where t.order_id = ts.order_id
and s.order_id = t.order_id
and b.bkr_cd = t.exec_broker
and sm.sec_id = t.sec_id
and t.status in ('acct')
and s.sec_typ_cd in ('com', 'mfc', 'pfd', 'cpfd', 'fcom', 'warr')
and t.trade_date >= '2011-05-01'
and t.trade_date <= '2011-05-31'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-18 : 16:35:28

Sum how? By total in resultset? By any kind of partition?

N 56°04'39.26"
E 12°55'05.63"

cherman
Starting Member

15 Posts

Posted - 2011-07-19 : 10:17:37

by the total in the result set using the group by.

I think I may have figured it out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 07:52:23

with cube or with rollup maybe?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 09:30:45

Or using a windowed function?

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-21 : 07:00:13