Groupung and Counting items in queries

Hi
I have been wrestling with this issue for a while now, and my solution
is not very elegant.

I have a very l;rge database with data like this (>1,000, 000 records
now)


GrpID GEName SampleID
A C 234
A C 235
A C 236
A B 237
A D 238
A D 239
A D 240
A D 241


What I want to produce is a summary of the count of unique GEName and
SampleID by GrpID

Result should be (in example above)

GrpID GEName SampleID
A 3 8


Currently I'm using 4 queries to accomplish this

1) GROUP BY GrpID, COUNT SampleID

2) GROUP BY GrpID and GEName, then 3) using #2, GROUP BY GrpID COUNT
GEName


Then I join 1) and 3) by GrpID

to result in GROUP BY GrpID, Count of GEName, Count of SampleID

There must be a quicker, simpler way to do this, because this solution
is not elegant and sloppy.

Suggestions welcome

Can I nest GROUP within a COUNT?

Thanks
Jerry

re: Groupung and Counting items in queries

And I need to save the results in a table


On Sun, 12 Mar 2006 07:47:34 -0600, Jerome Ranch <ranchjp@mchsi.com>
wrote:
[color=blue]
>Hi
>I have been wrestling with this issue for a while now, and my solution
>is not very elegant.
>
>I have a very l;rge database with data like this (>1,000, 000 records
>now)
>
>
>GrpID GEName SampleID
>A C 234
>A C 235
>A C 236
>A B 237
>A D 238
>A D 239
>A D 240
>A D 241
>
>
>What I want to produce is a summary of the count of unique GEName and
>SampleID by GrpID
>
>Result should be (in example above)
>
>GrpID GEName SampleID
>A 3 8
>
>
>Currently I'm using 4 queries to accomplish this
>
>1) GROUP BY GrpID, COUNT SampleID
>
>2) GROUP BY GrpID and GEName, then 3) using #2, GROUP BY GrpID COUNT
>GEName
>
>
>Then I join 1) and 3) by GrpID
>
>to result in GROUP BY GrpID, Count of GEName, Count of SampleID
>
>There must be a quicker, simpler way to do this, because this solution
>is not elegant and sloppy.
>
>Suggestions welcome
>
>Can I nest GROUP within a COUNT?
>
>Thanks
>Jerry[/color]

re: Groupung and Counting items in queries

Jerome Ranch wrote:
[color=blue]
> Hi
> I have been wrestling with this issue for a while now, and my solution
> is not very elegant.
>
> I have a very l;rge database with data like this (>1,000, 000 records
> now)
>
>
> GrpID GEName SampleID
> A C 234
> A C 235
> A C 236
> A B 237
> A D 238
> A D 239
> A D 240
> A D 241
>
>
> What I want to produce is a summary of the count of unique GEName and
> SampleID by GrpID
>
> Result should be (in example above)
>
> GrpID GEName SampleID
> A 3 8
>
>
> Currently I'm using 4 queries to accomplish this
>
> 1) GROUP BY GrpID, COUNT SampleID
>
> 2) GROUP BY GrpID and GEName, then 3) using #2, GROUP BY GrpID COUNT
> GEName
>
>
> Then I join 1) and 3) by GrpID
>
> to result in GROUP BY GrpID, Count of GEName, Count of SampleID
>
> There must be a quicker, simpler way to do this, because this solution
> is not elegant and sloppy.
>
> Suggestions welcome
>
> Can I nest GROUP within a COUNT?
>
> Thanks
> Jerry[/color]

3 queries, I think

Query 1. Group on GrpID, GEName, Count GEName

Query 2A. Group on GrpID, Sample, Count Sample
if you want to remove dups samples
else
Query 2B. Group on GrpID, Count Sample

Query 3. Table, Query1, QUery2. Relationships set between the 2
queries on ID. Make totals query if using 2A and sum on CountSample,
else DISTINCT if using 2B.

Select Query from the menu and select MakeTable if you want to store teh
results to a table.

re: Groupung and Counting items in queries

I was wondering if a subquery could be used?

Queries are joined on GrpID, main query counts GeName, the other
groups and counts SampleID in the criteria field
I'm testing this now

Jerry


On Sun, 12 Mar 2006 07:47:34 -0600, Jerome Ranch <ranchjp@mchsi.com>
wrote:
[color=blue]
>Hi
>I have been wrestling with this issue for a while now, and my solution
>is not very elegant.
>
>I have a very l;rge database with data like this (>1,000, 000 records
>now)
>
>
>GrpID GEName SampleID
>A C 234
>A C 235
>A C 236
>A B 237
>A D 238
>A D 239
>A D 240
>A D 241
>
>
>What I want to produce is a summary of the count of unique GEName and
>SampleID by GrpID
>
>Result should be (in example above)
>
>GrpID GEName SampleID
>A 3 8
>
>
>Currently I'm using 4 queries to accomplish this
>
>1) GROUP BY GrpID, COUNT SampleID
>
>2) GROUP BY GrpID and GEName, then 3) using #2, GROUP BY GrpID COUNT
>GEName
>
>
>Then I join 1) and 3) by GrpID
>
>to result in GROUP BY GrpID, Count of GEName, Count of SampleID
>
>There must be a quicker, simpler way to do this, because this solution
>is not elegant and sloppy.
>
>Suggestions welcome
>
>Can I nest GROUP within a COUNT?
>
>Thanks
>Jerry[/color]

re: Groupung and Counting items in queries

Hello,

Will this do what you want?

Query1:
SELECT Table1.GrpID, Table1.GEName, Count(Table1.SampleID) AS
CountOfSampleID
FROM Table1
GROUP BY Table1.GrpID, Table1.GEName;

Query2: (takes input from Query1 and creates table named Output)
SELECT Query1.GrpID, Count(Query1.GEName) AS CountOfGEName,
Sum(Query1.CountOfSampleID) AS SumOfCountOfSampleID INTO [Output]
FROM Query1
GROUP BY Query1.GrpID;

When you run Query2 it will automatically run Query1 first.