Group By Clause
Group By clause is a DML (data manipulation language) command used to combine data based on the value(s)
in the field(s) of a table(s). The Group By clause is most often used in combination with one or more aggregate functions.
The basic syntax of the GroupBy statement is:
Group By TableName.Field1, TableName.Field2
The Access groupby clause comes after the where clause of a query and before the optional having clause in the query:
Select TableName.FieldName1, Count(TableName.Field2),Avg(TableName.Field3) From TableName Where yourcriteria
Group
By TableName.FieldName1 Having somecriteria
Note: The ending semicolon is a required component of all SQL statements.
Now see the Access groupby clause with real column and table names:
Select M_Employees.Emp_Name, Sum(iif(Daily_Absence=true,1,0)), Avg(Daily_Hours) From M_Employees Where
M_Employees.Work_Date>=#01/01/05# and M_Employees.Work_Date<=#03/31/05# Group By M_Employees.Emp_Name Having Sum(iif(Daily_Absence=true,1,0))>3;
The above query will select distinct employee names whose daily absences total more than 3 during the 1st quarter of
2005 and will display the average of the hours worked per day during the same time period grouped by emp_name.
More Group By Query Examples and Discussion:
Aggregates are most often used in combination
with an SQL Group By clause. ... Note there is no Group By
which is normally associated with an aggregate function ...
www.blueclaw-db.com/accessquerysql/sql_aggregate_function.htm |
SQL Having clause: The having clause is used
in conjuction with the SQL group by clause and aggregate functions to
categorize and summarize data into groups ...
www.blueclaw-db.com/accessquerysql/having_clause_sql.htm |
