By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

group by clauses

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
"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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.