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.
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.