a question about "group by"

[Thomas Qi]

There is a basic sql below:
SELECT CTEnr AS ID, TimeStamp, DatagramSize, Source AS LocalIP,
Destination AS RemoteIP, Protocol, Messages, SourcePort AS LocalPort,
DestPort AS RemotePort
FROM SelectedRecs

I want to get results by grouping by the field LocalPort and
RemotePort, I try it like this:

SELECT CTEnr AS ID, TimeStamp, DatagramSize, Source AS LocalIP,
Destination AS RemoteIP, Protocol, Messages, SourcePort AS LocalPort,
DestPort AS RemotePort
FROM SelectedRecs
WHERE Source='137.72.43.114' and Destination='137.72.43.247'
GROUP BY LocalPort, RemotePort
ORDER BY TimeStamp;
It's error, then I modified it like this:

SELECT CTEnr AS ID, TimeStamp, DatagramSize, Source AS LocalIP,
Destination AS RemoteIP, Protocol, Messages, SourcePort AS LocalPort,
DestPort AS RemotePort
FROM SelectedRecs
WHERE Source='137.72.43.114' and Destination='137.72.43.247'
GROUP BY SourcePort, DestPort, CTEnr, TimeStamp, DatagramSize, Source,
Destination, Protocol, Messages
ORDER BY TimeStamp;
It works fine, but it's not grouping!

I want to know why? And, how can I get results by grouping by the field
LocalPort and RemotePort?
Best Regards,

Thomas

[Rick Brandt]

Thomas Qi wrote:

There is a basic sql below:
SELECT CTEnr AS ID, TimeStamp, DatagramSize, Source AS LocalIP,
Destination AS RemoteIP, Protocol, Messages, SourcePort AS LocalPort,
DestPort AS RemotePort
FROM SelectedRecs

I want to get results by grouping by the field LocalPort and
RemotePort, I try it like this:

SELECT CTEnr AS ID, TimeStamp, DatagramSize, Source AS LocalIP,
Destination AS RemoteIP, Protocol, Messages, SourcePort AS LocalPort,
DestPort AS RemotePort
FROM SelectedRecs
WHERE Source='137.72.43.114' and Destination='137.72.43.247'
GROUP BY LocalPort, RemotePort
ORDER BY TimeStamp;
It's error, then I modified it like this:

SELECT CTEnr AS ID, TimeStamp, DatagramSize, Source AS LocalIP,
Destination AS RemoteIP, Protocol, Messages, SourcePort AS LocalPort,
DestPort AS RemotePort
FROM SelectedRecs
WHERE Source='137.72.43.114' and Destination='137.72.43.247'
GROUP BY SourcePort, DestPort, CTEnr, TimeStamp, DatagramSize, Source,
Destination, Protocol, Messages
ORDER BY TimeStamp;
It works fine, but it's not grouping!

I want to know why? And, how can I get results by grouping by the
field LocalPort and RemotePort?
Best Regards,

Thomas

You must aggregate on all of the fields that you aren't grouping by. If you
have 50 records with the same value in the field(s) you are grouping on then for
every other field you have 50 possible rows to return. You have to tell the
query whether you want the Min, Max, Sum, First, etc., or it has no idea which
of those 50 possible row values to return.

If you group by all fields then you are not grouping at all.

In the query grid just specify some sort of aggregation besides Group By on all
of the fields that you are not grouping on. Yes this does mean that you might
not get everything you want out of the query. Totals queries are used for very
specific tasks and you sometimes have to combine them with other queries to get
all the data you want.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com