Group by

[MN]

Hello,
I have a table with 10 fields.
How can I make a query with group by just 3 fields.
Select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10
From table1
Group by f1,f2,f3
Thank you in advange.

 

[Wolfgang Enzinger]

Hi "MN",

I have a table with 10 fields.
How can I make a query with group by just 3 fields.
Select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10
From table1
Group by f1,f2,f3

that's impossible.

Imagine you only got 4 fields and 2 rows of data:

f1 f2 f3 f4
-- -- -- --
1 2 3 A
1 2 3 B

Now if a query like this would work:

SELECT f1,f2,f3,f4 FROM table GROUP BY f1,f2,f3

the result set would be:

f1 f2 f3 f4
-- -- -- --
1 2 3 ?? <<--

That's the problem - which value for f4 would you expect?

HTH,
Wolfgang

 

[Michel Walsh]

Hi,


On the other hand, if you are ready to get ANY row that belong to the
group, use:


SELECT f1, f2, f3, LAST(f4), LAST(f5), LAST(f6), LAST(f7), LAST(f8),
LAST(f9), LAST(f10)
FROM myTable
GROUP BY f1, f2, f3



Basically, LAST( ) on any field not making the GROUP BY.

Note that LAST does not mean the lastest record, but the last record seen by
the engine when solving the data query. Since that occurs per record, you
are sure that all LAST-fields will then come from the SAME record (but not
really from which one, if a group has many candidate records).



Hoping it may help,
Vanderghast, Access MVP

 

[Marshall Barton]

I have a table with 10 fields.
How can I make a query with group by just 3 fields.
Select f1,f2,f3,f4,f5,f6,f7,f8,f9,f10
From table1
Group by f1,f2,f3

What do you want to do with the other fields?

Generally, those extra fields will have different values
within a single group, so you can't just include them
because it would be ambiguous.

The most common thing to do is to aggregate them as a total
using Sum or ???

The query designer makes this fairly obvious by displaying
the Totals line in the design window.

 

[Wolfgang Enzinger]

Hi Michel,

SELECT f1, f2, f3, LAST(f4), LAST(f5), LAST(f6), LAST(f7), LAST(f8),
LAST(f9), LAST(f10)
FROM myTable
GROUP BY f1, f2, f3



Basically, LAST( ) on any field not making the GROUP BY.

wow! Never noticed these intrinsic FIRST() and LAST() functions
before! They can really be useful in this context sometimes. Thanks!

Wolfgang