Conditional Count

T

TraderAnalysis

I've been trying to figure this out for days as a somewhat of a novice in
Access. I am trying to count the # of issues that a trader (or occ_acro) has
PnL > 0. I can return the total number of issues that have PnL > 0 for the
whole database with this expression "Winners: (SELECT Count(Issue)FROM
ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL > 0)" but when I
try to add a GROUP BY ElectronicVolumeMonthly.occ_acro, I get an error.

What am I doing wrong?
 
J

Jason Lepack

Try this query and see if it does what you expect

select
EVM.occ_acro,
count(EVM.issue)
from
ElectronicVolumeMonthly as EVM
where
EVM.PnL > 0
group by
EVM.occ_acro

If it doesn't then post back with your table structure as well as what
exactly you want the query to do. Your current set up using the
subquery could be very debilitating if the data set gets large.

Cheers,
Jason Lepack
 
T

TraderAnalysis

Thanks for the quick response. I tried that but it came up with an error of
"You have written a subquery that can return more than 1 field without using
the EXISTS....." which I have never seen before.

My basic table setup has: occ_acro, issue, PnL, total volume (broken down
by numerous of catergories). In my query, I want to count the total number
of issues each trader is trading and then count the # of issues that are
"winners" which means have PnL > 0 and would like to sum the PnL of only
those winning issues.

Is seems so simple but yet I've wasted so much time on it.

Thanks for your help.
 
J

Jason Lepack

Create four queries:

qryIssues:
select
EVM.occ_acro,
count(EVM.issue) as Issues
from
ElectronicVolumeMonthly as EVM
group by
EVM.occ_acro

qryWinners:
select
EVM.occ_acro,
count(EVM.issue) as Winners
from
ElectronicVolumeMonthly as EVM
where
EVM.PnL > 0
group by
EVM.occ_acro

qrySumPnLOfWinners:
select
EVM.occ_acro,
sum(EVM.PnL) as SumPnL
from
ElectronicVolumeMonthly as EVM
where
EVM.PnL > 0
group by
EVM.occ_acro

qryCompareIssuesToWinners:
select
iss.occ_acro,
iss.Issues,
win.Winners
from
qryIssues as iss
join qryWinners as win
ON iss.occ_acro = win.occ_acro

Any issues, just post back.

Cheers,
Jason Lepack
 
T

TraderAnalysis

I am getting the same "You have written a subquery that can return more than
1 field without usingI completely follow the logic of the queries and am SOO frustrated as to why
this is not working. I do not have any other complexities within my main
query other than summing the trading volume column, counthing the issues and
I just want to have a subquery within this query to give me the subset of
"winners". Am I a lost cause?

Thanks
 
J

Jason Lepack

Oh, now I know your problem.

Create a new query in Design View. When it asks you to add a table,
just click close. Click "View"->"SQL View". Paste the code for one
of the queries.

That should work much better.

Cheers,
Jason Lepack
 
T

TraderAnalysis

Thanks. I thought I could get all (i.e. total issues AND # of winners) into
the same query. Not possible?
 
J

Jason Lepack

It's definitely possible. I was trying to figure out exaclty why you
were getting tripped up with the subquery. Now we'll get this done.

So you want to output the total number of issus and total number of
winners for a given occ_acro.

select

A.occ_acro,

(select count(B.issue)
from ElectronicVolumeMonthly as B
where A.occ_acro = B.occ_acro
group by B.occ_acro) AS issues,

(select count(C.issue)
from ElectronicVolumeMonthly as C
where A.occ_acro = C.occ_acro
and C.PnL > 0
group by C.occ_acro) AS winners,

(select sum(D.PnL)
from ElectronicVolumeMonthly as D
where A.occ_acro = D.occ_acro
and D.PnL > 0
group by D.occ_acro) as sumWinners

from

ElectronicVolumeMonthly as A

Try that...

Cheers,
Jason Lepack
 
T

TraderAnalysis

Ok, Jason, I must be doing something totally wrong. I can not figure out why
this isn't working. I apologize but I have no where else to turn. Anyway,
in the table I am querying off, I have thousands of records by month, by
issue, by occ_acro. I have a volume, PnL and risk associated wtih each
record. In my query, I am grouping by occ_acro and already have built in the
query: Occ_acro, total volume, net revene, Pnl per contract, Pnl per
contact, I already have a count for the issues in one column and in the last
column, I have Where the date is 03/30/2007. So my question is why can I get
the total # of issues that have PnL > 0 when I enter this: Winners: (SELECT
Count(Issue)FROM ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL >
0) but when I put a Group By ElectronicVolumeMonthly.occ_acro at then end, I
get an error and then when I put in your suggestions, I get the same "exists"
error.
Thanks
 
J

Jason Lepack

Copy the SQL and paste it here.

Ok, Jason, I must be doing something totally wrong. I can not figure out why
this isn't working. I apologize but I have no where else to turn. Anyway,
in the table I am querying off, I have thousands of records by month, by
issue, by occ_acro. I have a volume, PnL and risk associated wtih each
record. In my query, I am grouping by occ_acro and already have built in the
query: Occ_acro, total volume, net revene, Pnl per contract, Pnl per
contact, I already have a count for the issues in one column and in the last
column, I have Where the date is 03/30/2007. So my question is why can I get
the total # of issues that have PnL > 0 when I enter this: Winners: (SELECT
Count(Issue)FROM ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL >
0) but when I put a Group By ElectronicVolumeMonthly.occ_acro at then end, I
get an error and then when I put in your suggestions, I get the same "exists"
error.
Thanks










- Show quoted text -
Click to expand...
 
T

TraderAnalysis

SELECT PnL2007.PHYOFF, PnL2007.LNAME, PnL2007.FNAME,
ElectronicVolumeMonthly.occ_acro, PnL2007.GREV, PnL2007.TTLCOSTS,
PnL2007.NETREVB4OVERHEAD AS [Net Revenue Before Overhead],
Sum(ElectronicVolumeMonthly.[Total Volume]) AS [Trader Total Volume],
Avg(ElectronicVolumeMonthly.[Trader Share]) AS [AvgOfTrader Share], [Net
Revenue Before Overhead]/[Trader Total Volume] AS [PnL Per Contract],
Count(ElectronicVolumeMonthly.issue) AS [# of Issues], [Net Revenue Before
Overhead]/[# of Issues] AS [PnL Per Issue], Sum(PnL2007.RPOINTS) AS
SumOfRPOINTS, IIf([RPOINTS]=0,0,[RPOINTS]/[NETREVB4OVERHEAD]) AS [Risk
Percent], PnL2007.ORDERFLOW, IIf([ORDERFLOW]=0,0,[ORDERFLOW]/[TRADER TOTAL
VOLUME]) AS [PFOF per Contract], (SELECT Count(Issue)FROM
ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL > 0) AS Winners
FROM ElectronicVolumeMonthly INNER JOIN PnL2007 ON
ElectronicVolumeMonthly.occ_acro = PnL2007.ACRONYM
WHERE (((ElectronicVolumeMonthly.Month)=#3/30/2007#))
GROUP BY PnL2007.PHYOFF, PnL2007.LNAME, PnL2007.FNAME,
ElectronicVolumeMonthly.occ_acro, PnL2007.GREV, PnL2007.TTLCOSTS,
PnL2007.NETREVB4OVERHEAD, IIf([RPOINTS]=0,0,[RPOINTS]/[NETREVB4OVERHEAD]),
PnL2007.ORDERFLOW;
 
J

Jason Lepack

Sorry for not getting to you sooner. It has been a busy week.

When you use a subselect like that your query needs to return only one
record.

Put this in your winners column in design view:

Winners: SELECT COUNT(A.ISSUE) FROM ELECTRONICVOLUMEMONTHLY AS A
WHERE A.PNL > 0 AND A.occ_acro = ELECTRONICVOLUMEMONTHLY.occ_acro

Cheers,
Jason Lepack



SELECT PnL2007.PHYOFF, PnL2007.LNAME, PnL2007.FNAME,
ElectronicVolumeMonthly.occ_acro, PnL2007.GREV, PnL2007.TTLCOSTS,
PnL2007.NETREVB4OVERHEAD AS [Net Revenue Before Overhead],
Sum(ElectronicVolumeMonthly.[Total Volume]) AS [Trader Total Volume],
Avg(ElectronicVolumeMonthly.[Trader Share]) AS [AvgOfTrader Share], [Net
Revenue Before Overhead]/[Trader Total Volume] AS [PnL Per Contract],
Count(ElectronicVolumeMonthly.issue) AS [# of Issues], [Net Revenue Before
Overhead]/[# of Issues] AS [PnL Per Issue], Sum(PnL2007.RPOINTS) AS
SumOfRPOINTS, IIf([RPOINTS]=0,0,[RPOINTS]/[NETREVB4OVERHEAD]) AS [Risk
Percent], PnL2007.ORDERFLOW, IIf([ORDERFLOW]=0,0,[ORDERFLOW]/[TRADER TOTAL
VOLUME]) AS [PFOF per Contract], (SELECT Count(Issue)FROM
ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL > 0) AS Winners
FROM ElectronicVolumeMonthly INNER JOIN PnL2007 ON
ElectronicVolumeMonthly.occ_acro = PnL2007.ACRONYM
WHERE (((ElectronicVolumeMonthly.Month)=#3/30/2007#))
GROUP BY PnL2007.PHYOFF, PnL2007.LNAME, PnL2007.FNAME,
ElectronicVolumeMonthly.occ_acro, PnL2007.GREV, PnL2007.TTLCOSTS,
PnL2007.NETREVB4OVERHEAD, IIf([RPOINTS]=0,0,[RPOINTS]/[NETREVB4OVERHEAD]),
PnL2007.ORDERFLOW;



Jason Lepack said:
Copy the SQL and paste it here.
Click to expand...

- Show quoted text -
Click to expand...
 
Top