March 10, 2012 at 10:26 pm
Hi,
I am wondering why this following query doesn't work
select sum(convert(int, A.exeQty))/2 from
(select RPM.Account, RPM.Symbol, RPM.PandL, RMM.exeQty, RMM.Side, RMM.Creation_Date
from Redi_Position_Monitor RPM, Redi_Message_Monitor RMM
where
RPM.Position = 0 and
RPM.Symbol = RMM.Symbol and
RPM.Account = RMM.AccountNumber and
RPM.Creation_Date = RMM.Creation_Date) A
I get a error message: Column 'A.Account' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Whereas following query works fine
select sum(convert(int, A.exeQty))/2 from
(select RPM.Account, RPM.Symbol, RPM.PandL, RMM.exeQty, RMM.Side, RMM.Creation_Date
from Redi_Position_Monitor RPM, Redi_Message_Monitor RMM
where
RPM.Position = 0 and
RPM.Symbol = RMM.Symbol and
RPM.Account = RMM.AccountNumber and
RPM.Creation_Date = RMM.Creation_Date) A
Can someone explain how to make it work.
March 11, 2012 at 1:01 am
I can't spot the difference of the two queries. Both seem to be identical.
And why do you return columns in the subquery that aren't used in the outer query at all?
The following syntax should return the same result:
SELECT SUM(CONVERT(INT, exeQty)) / 2
FROM
Redi_Position_Monitor RPM
INNER JOIN Redi_Message_Monitor RMM
ON
RPM.Position = 0
AND RPM.Symbol = RMM.Symbol
AND RPM.Account = RMM.AccountNumber
AND RPM.Creation_Date = RMM.Creation_Date
March 11, 2012 at 3:12 am
both should work.
What's the version number of your SQLServer instance ?
select @@VERSION as SQLinstanceVersion
--or
Select Serverproperty('Edition') as Edition
, Serverproperty('ProductVersion') as ProductVersion
, Serverproperty('ProductLevel') as ProductLevel
, Serverproperty('IsClustered') as IsClustered
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2012 at 2:06 pm
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (...)
March 11, 2012 at 2:22 pm
Oops I posted a wrong query.
Working query:
select sum(convert(int, A.exeQty))/2 from
(select RPM.Account, RPM.Symbol, RPM.PandL, RMM.exeQty, RMM.Side, RMM.Creation_Date
from Redi_Position_Monitor RPM, Redi_Message_Monitor RMM
where
RPM.Position = 0 and
RPM.Symbol = RMM.Symbol and
RPM.Account = RMM.AccountNumber and
RPM.Creation_Date = RMM.Creation_Date) A
This query is not working:
select A.Account, A.Symbol, sum(convert(int, A.exeQty))/2 from
(select RPM.Account, RPM.Symbol, RPM.PandL, RMM.exeQty, RMM.Side, RMM.Creation_Date
from Redi_Position_Monitor RPM, Redi_Message_Monitor RMM
where
RPM.Position = 0 and
RPM.Symbol = RMM.Symbol and
RPM.Account = RMM.AccountNumber and
RPM.Creation_Date = RMM.Creation_Date) A
March 11, 2012 at 2:29 pm
There's a missing GROUP BY:
SELECT
A.Account,
A.Symbol,
SUM(CONVERT(INT, A.exeQty)) / 2
FROM
( SELECT
RPM.Account,
RPM.Symbol,
RPM.PandL,
RMM.exeQty,
RMM.Side,
RMM.Creation_Date
FROM
Redi_Position_Monitor RPM,
Redi_Message_Monitor RMM
WHERE
RPM.Position = 0
AND RPM.Symbol = RMM.Symbol
AND RPM.Account = RMM.AccountNumber
AND RPM.Creation_Date = RMM.Creation_Date
) A
GROUP BY A.Account, A.Symbol
March 11, 2012 at 2:34 pm
Thank you!!! Wow that works great!
If I want to divide into different buckets by size for example <1000, 1000-10,000 and >10000. Whats the best way to do this.
Thanks again...
March 11, 2012 at 2:38 pm
Something like this?
SELECT
A.Account,
A.Symbol,
SUM(CONVERT(INT, A.exeQty)) / 2,
CASE
WHEN SUM(CONVERT(INT, A.exeQty)) / 2 < 1000 THEN 'Chunk 1'
WHEN SUM(CONVERT(INT, A.exeQty)) / 2 < 10000 THEN 'Chunk 2'
ELSE 'Chunk 3'
END AS buckets
March 11, 2012 at 2:44 pm
Thanks!
Yes, What I want is in final table to have account, symbol and pnl depending on the size. PnL corresponding to trade quantity less that 1000, 1000 - 10000 and >10000. I want this for each account.
Thank you very much...
March 12, 2012 at 12:46 am
also, if you can, upgrade to at least SP1 ( current is SP1 CU4 )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply