Counting and grouping

Question

Post reply

Counting and grouping

russ_ashby

SSC Journeyman

Points: 77
More actions
November 15, 2004 at 3:13 pm

#61369

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,Classification
returns results like:
10100004.0 N
10100004.0 O
10100004.0 P
10100004.0 M
10100005.0 O
10100007.0 O
remember 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) = 1
10100007.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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

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

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Shaunio Old Hand Points: 316 More actions · Answer 2

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

?

russ_ashby SSC Journeyman Points: 77 More actions · Answer 3

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.

russ_ashby SSC Journeyman Points: 77 More actions · Answer 4

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

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)

Henk Kok SSC Eights! Points: 849 More actions · Answer 5

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

Daniel Stefaniu SSC Enthusiast Points: 123 More actions · Answer 6

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

Daniel Stefaniu SSC Enthusiast Points: 123 More actions · Answer 7

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