"Group by" confusion in query

T

tom

When I hit the totals button all the fields then show group
by.

I want to sum one field grouped by another field. Access complains whenever
I change the total: space to a blank.
So apparently Access is insisting that I group by all the fields which is
not what I want to do.
 
D

Dirk Goldgar

tom said:
When I hit the totals button all the fields then show group
by.

I want to sum one field grouped by another field. Access complains
whenever I change the total: space to a blank.
So apparently Access is insisting that I group by all the fields which is
not what I want to do.
Click to expand...


The Totals: line is where you specify everything to do with grouping and
calculations performed on the aggregate. When you first switch to a totals
query, Access just sets all the fields to "Group By", but that's just a
suggestion. If you want to sum that column, put "Sum" in that cell. The
dropdown list you can get when the cursor is in that cell gives you the list
of aggregate functions you can specify. But you can't just leave it blank
for any field you want to include in the query results.
 
D

Douglas J. Steele

Sure. The only rule is that every field mentioned in the query must either
have an aggregate function applied to it, or else must be in the GROUP BY
clause.

To sum C, grouping by A, the SQL would be:

SELECT A, Sum(C)
FROM MyTable
GROUP BY A
 
T

tom

Then I get the error:

"You tried to execute a query that doesn't include the
specified expression 'B' as part of an aggregate function."
 
D

Dirk Goldgar

(re-posting, as my original reply hasn't appeared)

tom said:
Then I get the error:

"You tried to execute a query that doesn't include the
specified expression 'B' as part of an aggregate function."
Click to expand...


Doug's SQL cannot possibly give you that error. I guess that you are
working in design view, and you have a check mark in the "Show:" cell
underneath the field B, and don't have anything in the "Total:" cell. As I
said earlier, if have include a field in the query results (which you do by
checking the "Show:" box), then you must either Group By that field or apply
one of the aggregate functions to it.
 
J

John W. Vinson

http://www.infogoal.com/sql/sql-aggregate.htm

I was looking this stuff up on the web.

Does this mean that Access cannot do the things
that are done in the above link?

For example

Table with three fields:

A,B,C

I can't sum C and group by A?
Click to expand...

Certainly, but what do you want to do with B?

Suppose you have data:

A;B;C
"Texas"; "Red"; 100
"Texas"; "Blue"; 50
"Iowa"; "Green"; 100
"Iowa"; "Yellow"; 350
"Iowa"; "Orange"; 25

What result do you want to see? Texas and 150, Iowa and 475, sure; but what do
you want in the B column?

Perhaps you could post a real example of your data and your desired result.
 
T

tom

John W. Vinson said:
Certainly, but what do you want to do with B?

Suppose you have data:

A;B;C
"Texas"; "Red"; 100
"Texas"; "Blue"; 50
"Iowa"; "Green"; 100
"Iowa"; "Yellow"; 350
"Iowa"; "Orange"; 25

What result do you want to see? Texas and 150, Iowa and 475, sure; but
what do
you want in the B column?

Perhaps you could post a real example of your data and your desired
result.
Click to expand...

I want the B column to remain as is.

This is an invoice. A is the invoice number and C is an amount. B is the
same for every A.
 
D

Dirk Goldgar

tom said:
I want the B column to remain as is.

This is an invoice. A is the invoice number and C is an amount. B is the
same for every A.
Click to expand...


In that case, you can either Group By B, or else use the First or Last
aggregate function (equally arbitrary) to pull one of the B values that
appears for the group and use it. For example, this SQL:

SELECT A, First(MyTable.B) As B, Sum(C)
FROM MyTable
GROUP BY A
 
T

tom

Dirk Goldgar said:
In that case, you can either Group By B, or else use the First or Last
aggregate function (equally arbitrary) to pull one of the B values that
appears for the group and use it. For example, this SQL:

SELECT A, First(MyTable.B) As B, Sum(C)
FROM MyTable
GROUP BY A


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

Thank you.

I have many fields in this table.

I am wondering if it is better design to just create a query
with two fields to get my sum value for the invoice amount, and then use
these values in the final query.
Thus there would be two relatively simple queries.

Or should I just use one complex query with your method?
 
D

Dirk Goldgar

tom said:
Thank you.

I have many fields in this table.

I am wondering if it is better design to just create a query
with two fields to get my sum value for the invoice amount, and then use
these values in the final query.
Thus there would be two relatively simple queries.

Or should I just use one complex query with your method?
Click to expand...


I don't have enough information to say, since everything we've seen so far
has been in reference to an example table with three fields. I will say
that the number of fields included in a query isn't a measure of its
complexity. From my point of view, any query that includes only single
table, and doesn't include any subqueries, is a simple query no matter how
many fields it includes from that table, and if I can solve my problem with
that query, I'm a happy developer.

So to me, it makes sense that, if the data I need for my invoice is all
there in this one table, I may as well write one simple query to extract it.
Sure, I'll exclude any fields I don't need, but for the ones that I do need,
it's no skin off my nose if I have to group by some of them even though they
are invariant within the logical group.
 
T

tom

Dirk Goldgar said:
I don't have enough information to say, since everything we've seen so far
has been in reference to an example table with three fields. I will say
that the number of fields included in a query isn't a measure of its
complexity. From my point of view, any query that includes only single
table, and doesn't include any subqueries, is a simple query no matter how
many fields it includes from that table, and if I can solve my problem
with that query, I'm a happy developer.

So to me, it makes sense that, if the data I need for my invoice is all
there in this one table, I may as well write one simple query to extract
it. Sure, I'll exclude any fields I don't need, but for the ones that I do
need, it's no skin off my nose if I have to group by some of them even
though they are invariant within the logical group.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...

So in general the fewer subqueries the better?
 
D

Dirk Goldgar

tom said:
So in general the fewer subqueries the better?
Click to expand...


I don't know if one can say that. All I'm really comfortable saying is that
a query involving a single table is bound to be more efficient, other things
being equal, than one that involves multiple tables (or the same table
multiple times). But other things are so seldom equal! Everything depends
on how the query will actually be executed, and that depends on how the
database engine interprets the query, optimizes it, and designs its
execution plan. Sometimes the execution time of a complex query, that takes
a long time to run, can be vastly reduced by rewriting it into a
semantically equivalent form which nevertheless allows the query engine to
develop a more efficient execution plan.

But a lot of queries that look superficially very different nevertheless are
executed internally in the exact same way. So it's not usually worth
fretting over the exact execution plan unless one is confronted with a
query that is really running slowly; and even then the most common solution
is to create indexes for some unindexed fields that are being joined,
filtered, or sorted on.
 
G

gaetano

tom said:
When I hit the totals button all the fields then show group
by.

I want to sum one field grouped by another field. Access complains
whenever I change the total: space to a blank.
So apparently Access is insisting that I group by all the fields which is
not what I want to do.
Click to expand...
 
Top