Counting and grouping
-
First post here so I hope I've got the right forum.
I'm trying to accomplish something in one query rather than use two and I wonder if it's possible.
Two tables.
a (multiple records for each account)
account
event
b
event
classification
Here's what I'm trying to do. Pull the classification for the event in table a and select records that only have one type of classification (i.e. Only attend one type of event)
So I figure if I group on the classification I get a set of rows for an account each with a classification:
SELECT a.raccount,
b.Classification
FROM master_tix_header a,tblProductType b
WHERE revent = Event AND (master_tix_header.ryear >= 1999 AND master_tix_header.ryear <= 2002)
GROUP BY raccount,Classificationreturns results like:
10100004.0 N
10100004.0 O
10100004.0 P
10100004.0 M
10100005.0 O
10100007.0 Oremember I only want records where they have only one type of classification. So I think:
SELECT a.raccount,
b.Classification
FROM master_tix_header a,tblProductType b
WHERE revent = Event AND (master_tix_header.ryear >= 1999 AND master_tix_header.ryear <= 2002)
GROUP BY raccount,Classification HAVING count(Classification) = 110100007.0 O
What happened to 10100005.0 ? Well, it disappears because I'm selecting records that only have 1 event in master_tix_header rather than 1 type of event.
I must be doing something stupid. Can anyone help?
(The two query version involves saving the first query and counting the number of Classifications for each record from that query - there must be a way to do it in one!)
Many thanks
Russ
-
What is the relationship between tables a and b - is it on event?
So, in English terms, table a is an 'Account Events' table and table b is an 'Event Classification' table?
Therefore, if an account has more than 1 record in table a, does that automatically mean that it has more than 1 classification?
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Hi, out of curiosity, does this return the correct data?
SELECT a.raccount,b.Classification
FROM master_tix_header a
JOIN tblProductType b ON a.revent = B.Event
WHERE a.ryear BETWEEN 1999 AND 2002
GROUP BY a.raccount, b.Classification HAVING COUNT(*) = 1
?
-
It doesn't, I'm afraid. 10100005 is still excluded because it has more than one record filling the criteria in the master_tix_header table even though both records are of type O.
-
That's correct yes. The join is on the Event. And technically yes, if there is more than one record in a there is more than one classification in the result set even though they could all be of the same type.
e.g.
10100005 attended two events AAIC, AAHC. Both type O. So without grouping my result is
10100005 O
10100005 O
10100007 only attended one event so without grouping the result is
10100007 O
so grouping gives me:
10100005 O
10100007 O
So in my mind I think, okay, select records that only have one classification but this only gives me 10100007 because it has one classification AND one record in table a.
Hence I have to run two queries. One to provide:
10100005 O
10100007 O
10100008 P
10100008 O
And the other to select those from the above set that only have 1 classification (i.e. exclude 10100008)
-
Hi Russ,
I think this should do the trick. If not I must have misunderstood you, so please clarify.
select a.raccount, b.classification
-- count the number of visits
-- for this class of events if you like
, count(1) number_of_visits
from master_tix_header a
, tblProductType b
where a.revent = b.event
and a.ryear between 1999 and 2002
-- this is the essential condition:
-- there may not exist an event record
-- with another classification
-- for this raccount
and not exists
(select 1
from master_tix_header a2
, tblProductType b2
where a2.revent = b2.event
and a2.raccount = a.raccount
and b2.classification != b.classification
)
group by a.raccount, b.classification
Cheers,
Henk
-
having your select
SELECT a.raccount,
b.Classification
FROM master_tix_header a,tblProductType b
WHERE revent = Event AND (master_tix_header.ryear >= 1999 AND master_tix_header.ryear <= 2002)
GROUP BY raccount,Classification
with the results
10100004.0 N
10100004.0 O
10100004.0 P
10100004.0 M
10100005.0 O
10100007.0 O
(as you mentioned)
U can try
select max(raccount) AS raccount, MAX(Classification) AS Classification
FROM (
/* your select */
SELECT a.raccount, b.Classification
FROM master_tix_header a,tblProductType b
WHERE revent = Event AND (master_tix_header.ryear >= 1999
AND master_tix_header.ryear <= 2002)
GROUP BY raccount,Classification
/*\your select */
) a
HAVING COUNT(*) = 1
it should work because the "max" will not change anything ... and the record only come once as a result of the group
Daniel
-
sorry, missed a Group by
select raccount AS raccount, MAX(Classification) AS Classification
FROM (... ) a
GROUP BY raccount
HAVING COUNT(*) = 1
if you are not convinced, try adding a new column :
.... AS Classification, count(*) AS c ....
and remove the HAVING clause
good luck
Daniel
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply