beginner sql - how to select a sum, but multiply that by another column
-
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?
-
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.
-
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.
-
You may try to the insert the first set of results into a temp table them multiple from the temp table.
-
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
-
Use this:
Sum([tblTraderPnL].[Net P&L] * [tblTradePayout].[Payout])
-
oh man... thanks...
i'm retarded - just didn't realize at first that that's the same thing...
tyvm lynn
-
You are welcome. I like math!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply