Results 1 to 2 of 2
2003-04-08, 20:52 #1
- Join Date
- Mar 2003
- Toronto, Canada
- Thanked 0 Times in 0 Posts
Difference Between Where and Group By (2002)
I am looking for a more logical defination to explain the difference between Where and Group by in queries when using totals. I have read the access 2002 bible and i swear it is like a different language. I would like to know the difference and when to use each other.
Any light on this subject would help.
2003-04-08, 21:15 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Difference Between Where and Group By (2002)
WHERE can be used in any query (not only Totals queries) to specify conditions that must be satisfied. For example <font face="Georgia">SELECT LastName, FirstName FROM tblPersons WHERE Age > 18</font face=georgia> will return the fields LastName and FirstName from the table tblPersons for those records for which Age is over 18.
GROUP BY is only used in a specialized type of query called a Totals query to specify that the records must be aggregated by a field. That means that not every individual record will be returned, but only one for each unique combination of the GROUP BY fields. For example <font face="Georgia">SELECT LastName, Avg(Age) As AverageAge FROM tblPersons GROUP BY LastName</font face=georgia> will return one record for each unique last name in the table, together with the average age of the persons with that last name.
If you only want to calculate the average age for each last name of persons over 18, you would use <font face="Georgia">SELECT LastName, Avg(Age) As AverageAge FROM tblPersons WHERE Age > 18 GROUP BY LastName</font face=georgia>. This means that first persons over 18 years of age are selected, then the average is calculated.
The query language also has a keyword HAVING. This is also used to specify a condition, but this is checked after the aggregation has been executed, whereas WHERE is evaluated before the aggregation. Say that you only want to return last names for which the average age of the persons in the table is over 18 years. You would use <font face="Georgia">SELECT LastName, Avg(Age) As AverageAge FROM tblPersons GROUP BY LastName HAVING Avg(Age) > 18</font face=georgia>. Note the difference: it is very well possible that some of the last names returned by this query belong to persons of less than 18 years old. But the average age for each returned last name must be over 18 years.