beginner sql - how to select a sum, but multiply that by another column

Question

Post reply

beginner sql - how to select a sum, but multiply that by another column

Gregory Tran

SSC Veteran

Points: 200
More actions
March 13, 2007 at 8:16 am

#72641

SELECT DISTINCTROW [tblTraderNames].TraderName, (Sum([tblTraderPnL].[Net P&L]))*([tblTraderPnL].[Net P&L])
in the above statement, i get an error about aggregate functions...
if i use (Sum([tblTraderPnL].[Net P&L]))*(.5), it works fine... is it possible for me to do those kinds of calcs?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Try this:

sum([tblTraderPnL].[Net P&L] * [tblTraderPnL].[Net P&L])

Don't know why you would want to multiply the the sum of the field by the field.

Julie Hargraves SSCarpal Tunnel Points: 4457 More actions · Answer 2

I'm not really sure what you're trying to accomplish here... I imagine you're grouping by TraderName and multiplying the Sum of each TraderName's [Net P&L] values by which value of [Net P&L]? All of them for each TraderName? I think we need a little more information on this one. Sample data and expected results would help a lot.

Perry Harts Ten Centuries Points: 1271 More actions · Answer 3

You may try to the insert the first set of results into a temp table them multiple from the temp table.

Gregory Tran SSC Veteran Points: 200 More actions · Answer 4

ok here is my statement in entirety

SELECT DISTINCTROW [tblTraderNames].TraderName, (Sum([tblTraderPnL].[Net P&L]))*(.9) AS [Money] , 'Net P&L' AS [Memo]

INTO tblTempReportNetPnL

FROM (tblTraderNames INNER JOIN tblTraderPnL ON tblTraderNames.TraderID = tblTraderPnL.TraderID) INNER JOIN tblTraderPayout ON (tblTraderPnL.TraderID = tblTraderPayout.TraderID) AND (tblTraderNames.TraderID = tblTraderPayout.TraderID)

**************************************************

so this is supposed to pull from 3 tables... the tblTraderNames just has a traderID and a name, and the other 2 tables have the trader id w/ couple other columns fpr PnL, etc...

Sum([tblTraderPnL].[Net P&L]))*(.9) <---- so there, instead of the .9, i want to pull the value i have for the payout percentage from my tblTraderPayout, so i actually want something more like

Sum([tblTraderPnL].[Net P&L]))*([tblTradePayout].[Payout]) because i want to sum the PnL numbers from each day, then multiply it by the multiplier assigned to each person.

My tables are set as follows:

tblTraderNames = TraderID, TraderName

tblTraderPayout = TraderID, Payout

tblTraderPnL = ID, TraderID, NetP&L

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Use this:

Sum([tblTraderPnL].[Net P&L] * [tblTradePayout].[Payout])

Gregory Tran SSC Veteran Points: 200 More actions · Answer 6

oh man... thanks...

i'm retarded - just didn't realize at first that that's the same thing...

tyvm lynn

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

March 13, 2007 at 9:30 am

#694728

You are welcome. I like math!