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 4 1618
And I need to save the results in a table
On Sun, 12 Mar 2006 07:47:34 -0600, Jerome Ranch <ra*****@mchsi.com>
wrote: 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
Jerome Ranch wrote: 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
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.
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 <ra*****@mchsi.com>
wrote: 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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Leif |
last post by:
I've seen some pages with the footer:
This page was created in 0.003s using 123 queries.
Is there a way to get the number of queries on a single connection?
I could fetch the value of "SHOW...
|
by: edwardfredriks |
last post by:
I'm looking for a script that, instead of counting down, can "count up"
from a given date. So the output should be something like "(xx) days
since (date/event)" or "(date/event) was (xx) days ago"....
|
by: Chad Reid |
last post by:
Hello,
I have a bit of code that obviously doesn't work, but I need
help creating a query that would have the same effect as if this
query was working.
SELECT * FROM
(SELECT Count(*) AS...
|
by: chrisc |
last post by:
Hello,
Hope this is the right place for this...
I am creating a testing database for components as they come off a
production line.
My reports need to select faults that are found, as well...
|
by: ChadDiesel |
last post by:
I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other...
|
by: Dan |
last post by:
Is this discouraged?:
for line in open(filename):
<do something with line>
That is, should I do this instead?:
fileptr = open(filename)
for line in fileptr:
<do something with line>
|
by: Howard |
last post by:
I often see a message like this on the bottom of web pages
page generated in 0.04 seconds used 5 queries
how do they count the number of queries
?
Howard
|
by: nsymiakakis |
last post by:
Hi everyone, I have two seperate queries that work great individually, but I need to join them together to get a combined output. Can someone look at these and help me with the code? Here are the 2...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |