group by clauses

[C L Humphreys]

Hi,

Anyone have a really good reference for learning group by/having clauses in
sql? I'm having real trouble getting my head around them.

I can run a query with say, 2 columns, grouping by one and using count() in
the other, but whenever I add another column it says it's not part of an
aggregate function. I don't think I want it to be but there must be another
way around it.

For example I can create this result.

Code Number
X 2
Y 2
Z 3

But now I want this result

ID Code Number
1 X 2
2 X 2
3 Y 2
4 Y 2
5 Z 3
6 Z 3
7 Z 3

Thanks in advance,
Chris

[Ray]

"C L Humphreys" <cl*********@toofgib.moc> wrote in message news:<bm**********@ucsnew1.ncl.ac.uk>...

Hi,

Anyone have a really good reference for learning group by/having clauses in
sql? I'm having real trouble getting my head around them.

I can run a query with say, 2 columns, grouping by one and using count() in
the other, but whenever I add another column it says it's not part of an
aggregate function. I don't think I want it to be but there must be another
way around it.

For example I can create this result.

Code Number
X 2
Y 2
Z 3

But now I want this result

ID Code Number
1 X 2
2 X 2
3 Y 2
4 Y 2
5 Z 3
6 Z 3
7 Z 3

Thanks in advance,
Chris

Hello Chris,

Perhaps what you could do is use two queries to achive
your results.

1. qry01GroupCodes = Your souce query which is the same as
you produced in your sample result above.

Code Number
X 2
Y 2
Z 3

2. Next, qry02GroupCodesResult = Use the same source as you
did for "qry01..." and bring in "qry01.." as your second source.
You should have two objects to source from.

Link the code fields together and use the fields from your first
source,"ID", in your QBE Grid, with your "Code" field and "Number"
fields coming from your second souce. Then run the query.

You should get the following results:

ID Code Number
1 X 2
2 X 2
3 Y 2
4 Y 2
5 Z 3
6 Z 3
7 Z 3

Have fun experimenting.

Regards,

Ray

[Pieter Linden]

"C L Humphreys" <cl*********@toofgib.moc> wrote in message news:<bm**********@ucsnew1.ncl.ac.uk>...

Hi,

Anyone have a really good reference for learning group by/having clauses in
sql? I'm having real trouble getting my head around them.

I can run a query with say, 2 columns, grouping by one and using count() in
the other, but whenever I add another column it says it's not part of an
aggregate function. I don't think I want it to be but there must be another
way around it.

For example I can create this result.

Code Number
X 2
Y 2
Z 3

But now I want this result

ID Code Number
1 X 2
2 X 2
3 Y 2
4 Y 2
5 Z 3
6 Z 3
7 Z 3

Thanks in advance,
Chris

use 2 queries. One that does the top query that returns the count per
code. Then create another query where you join that result to the
original table that contains the ID... nothing doing.