Practicing Group By - Advice welcome

[bobunknown]

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]

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

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]

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]

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]

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]

Roger that,

Thanks for the advice.

 

[CJ_London]

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