Grouping values in SQL
When analyzing data, creating reports, and many other functions, it is necessary to group information in SQL. Getting the maximum or minimum value, the total, or the average, of a column is a very common requirement.
This is done by grouping the items, and requesting the appropriate result. In the above cases, they would be:
MAX(FIELD) – Maximum value
MIN(FIELD) – Minimum value
AVG(FIELD) – Average value
SUM(FIELD) – Total of all values
If, for example, we assume we have TABLE1, which has columns for STORE_NUMBER, SALE_DATE, and SALE_AMOUNT. If you want to get all of these values, for each store, by day, the query would be:
SELECT STORE_NO as ‘Store’,
SALE_DATE as ‘Date’,
MIN(SALE_AMOUNT) as ‘Min sale’,
MAX(SALE_AMOUNT) as ‘Max sale’,
SUM(SALE_AMOUNT) as ‘Total sales’
GROUP BY STORE_NUMBER,SALE_DATE
The result of this will be one record for each store, for each date. These fields will be followed by the minimum, maximum, and average, sale amount of all the records for that store on that date. The final field will be the total sales for that store on that date.
Please note that if you do not us the "as" when using the min, max, etc., functions, the fields will be labeled Field1, Field2, and so on.
There are some basic caveats regarding the grouping of records. The fields specified in the "GROUP BY" clause must not use any of the grouping functions. You can not use "MIN(STORE_NUMBER)" if you are using "GROUP BY STORE_NUMBER", for example. Any field not in the "GROUP BY" clause, must use a grouping function, such as min, max, etc. In other words, you an not use "SALE_AMOUNT" in place of "MIN(SALE_AMOUNT)" in the above example, because SALE_AMOUNT is not in the "GROUP BY" clause.