467,869 Members | 1,392 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,869 developers. It's quick & easy.

group by clauses

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
  • viewed: 3153
Share:
2 Replies
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
"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.

Similar topics

10 posts views Thread by Randell D. | last post: by
3 posts views Thread by Robby McGehee | last post: by
2 posts views Thread by aj70000 | last post: by
5 posts views Thread by bbembi_de | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.