- database solutions and downloads for microsoft access

SQL Group By

GROUP BY: SQL's Organizational Clause

GROUP BY is an invaluable SQL clause for organizing and performing operations on information about several different items you want to aggregate. For instance, what if you wanted to get information about employee attendance in several different time periods to do comparisons and charting? Or what if you wanted to get the sales figures for several salespeople with a single SQL phrase?

SQL GROUP BY is the proper choice when you're selecting multiple columns from one or more tables, and also performing a mathematical operation while selecting them. You must GROUP BY all other columns except for the one with a mathematical operator. You can't do more than one mathematical operation. And you can't group by columns with Memo, General, or Blob field properties.

This function allows you to organize information returned so that your information is easy to use, even ready to print out as a report.

The SQL Group By syntax is:

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"

In this syntax, "column_name1" represents one or more columns returned by your query. It can be a field in table named by the FROM statement, a table alias from the SELECT list, or a numeric expression indicating the position of the column, starting with 1 to the left.

This search would return a well-organized table that takes your entire first column's data and aggregate it by name, finding the sums of all the second column figures that had a matching first column.

For instance, if you had a table containing information on several different stores and their sales figures, you could figure out the sales of each by using the following operation:

SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name

The result would be the store name in the first column and the sum total of sales for each in the second column. You could further restrict the sum by time periods.

You can use operations besides the SUM function. For instance, you can use COUNT to perform a simple count of inventory items, of employees, or of filtered items. If you needed to know how many products you have in the $200-400 price range, you would perform the following:

SELECT items, COUNT (*) as "$200-400 Inventory Items"
FROM inventory
WHERE price>= 200 AND price<= 400
GROUP BY items

Your returned information would be a one-row table telling you how many items you have in that price range.

You can perform similar functions using MIN and MAX

Multiple Grouping Columns:

When you're using several columns to group by, you need to be careful about ordering. For instance, if you want the average salary of each job by department, you'll use the same two columns you would use if you wanted the average salary of each department by job – but the second dataset would be used differently from the first one.

SELECT department, job 
AVG salary as "Average Salary"
FROM employeelist
GROUP BY department, job

This query would return average salaries specified by departments and jobs within each department.