Sum on two accounts

bknight
BronzeLounger
Posts: 1364
Joined: 08 Jul 2016, 18:53

Sum on two accounts

Post by bknight »

I have a query and for Hans a form that sums a daily P&L on one account. Well I muddlied the waters and added a second account. So I would like tohave each account summed daily.
here is the current sum, account is not part of the query, but both now need to be added

Code: Select all

SELECT Int(([NinjaTrader2024].[Time])) AS Tradedate, NinjaTrader2024.Instrument, Sum(NinjaTrader2024.Profit) AS [Daily P_L], Sum(Abs([Quantity])) AS [#Trades], Sum([commission]+[fees]) AS [Com&Fee]
FROM NinjaTrader2024
WHERE (((Day([NinjaTrader2024].[Time]))=Day(Now())) AND ((Month([NinjaTrader2024].[Time]))=Month(Now())))
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.Instrument, Year([NinjaTrader2024].[Time]), NinjaTrader2024.Account_display_name
HAVING (((Year([NinjaTrader2024].[Time]))=Year(Now())));
I attempted just to add the account field, but then I got an ambiguous error concerning quantity, I forgot to copy the error, but from the code you may be able to determine the course of coding.

User avatar
HansV
Administrator
Posts: 78350
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sum on two accounts

Post by HansV »

Does this do what you want?

SELECT NinjaTrader2024.Instrument, NinjaTrader2024.Account_display_name, Sum(NinjaTrader2024.Profit) AS [Daily P_L], Sum(Abs([Quantity])) AS [#Trades], Sum([commission]+[fees]) AS [Com&Fee]
FROM NinjaTrader2024
WHERE Int([NinjaTrader2024].[Time])=Date()
GROUP BY NinjaTrader2024.Instrument, NinjaTrader2024.Account_display_name
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1364
Joined: 08 Jul 2016, 18:53

Re: Sum on two accounts

Post by bknight »

Yes that worked, thanks