Practicing Group By - Advice welcome (1 Viewer)

bobunknown · Jan 10, 2019

Hi all just trying to expand my knowledge of group by and other such functions so I can get useful information from my data and I would appreciate some help.

The attachment shows a sample of data I'm working with, just two columns to start; Bin ID & ID.

My aim, to count the Bin ID's but to have them grouped by ID.

My best guess would be
Select both fields...
Count Bin ID...
From Bins(the table)...
Group by ID...

on the money? ooooor way off? advice and examples would be appreciated.

CJ_London · Jan 10, 2019

you only need to select the ID field and count the binsID field, you don't need to select

Code:

SELECT ID, Count(BinID) as CountofBinID
FROM Bins
GROUP BY ID

recommend don't use spaces in names and give names meaning (ID? ID of what?)

plog · Jan 10, 2019

Select both fields

Incorrect. You would only SELECT the field you want to GROUP BY.

In fact your query will blow up if you did it like you suggested. Good rule:

Every field in the SELECT that isn't part of an aggregate function (SUM, COUNT, MAX, etc.) must be in the GROUP BY.

bobunknown · Jan 10, 2019

Thanks for the reply's,

Every field in the SELECT that isn't part of an aggregate function (SUM, COUNT, MAX, etc.) must be in the GROUP BY

So using this method as part of a larger more complex query could be complicated, would you suggest when I need to carry outhits function simply doing it as a sub query?

SELECT ID, Count(BinID) as CountofBinID
FROM Bins
GROUP BY ID

Thanks CJ this is a nice simple example of the syntax for me to follow.

plog · Jan 10, 2019

Yes, divide and conquer. Sometimes, that's the only way to achieve what you want. Othertimes it just makes it simpler for you as a coder to understand what each piece is doing and getting that piece done correctly.

bobunknown · Jan 10, 2019

Roger that,

Thanks for the advice.

CJ_London · Jan 10, 2019

until you understand the syntax better, always a good idea to use the query builder to get the result you require, then look at the sql window to see what has been generated. Access tends to overdo brackets and table names, but with good reason to avoid possible ambiguity

Practicing Group By - Advice welcome (1 Viewer)

bobunknown

Registered User.

Attachments

CJ_London

Super Moderator

plog

Banishment Pending

bobunknown

Registered User.

plog

Banishment Pending

bobunknown

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread