Conditional Count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
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
 
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
 
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
 
Thanks. I thought I could get all (i.e. total issues AND # of winners) into
the same query. Not possible?
 
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
 
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
 
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...
 
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;
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If statement with checkbox 3
Subform total on Main Form 7
Excel VBA - counting cells by colors and names to it in two different rows. 0
Desperate to filter main form 1
Counting the number of Matching items in a table 3
Multiple LEFT Joins w/ COUNT... getting really wierd results 1
How to Union Crosstabs... 1
Counting in Query 3

Back
Top