How to classify and group databases and aggregate SQL information

You need to know how to use some SQL clauses and operators to arrange SQL data for efficient analysis. The following suggestions show you how to create a statement to obtain the expected results.
Arranging data in a meaningful way may be a challenge. Sometimes you only need to perform simple classification. Generally, you must perform more processing-grouping for analysis and total. Fortunately, SQL provides a large number of clauses and operators for classification, grouping, and total. The following suggestions help you understand when to classify, when to group, when and how to aggregate. For more information about each clause and operator, see online books.
#1: Sort by category
Generally, we do need to sort all the data. The order by clause of SQL sorts data BY letters or numbers. Therefore, similar data is clearly classified into different groups. However, these groups are only the results of classification, and they are not real groups. Order by displays each record, and a group may represent multiple records.
#2: Reduce similar data in a group
The biggest difference between a category and a group is that the classification data shows (within any limitation) all records, and the group data does not show these records. The group by clause reduces similar data in a record. For example, group by can return a unique ZIP code list from the source file with repeated values:
SELECT ZIP
FROM MERS
GROUP BY ZIP
Only the columns that contain GROUP definitions in the group by and SELECT columns. In other words, the SELECT list must match the GROUP list. Only one exception is that the SELECT list can contain aggregate functions. (Group by does not support aggregate functions .)
Remember, group by does not classify the groups produced as results. Add an order by clause (#1) to sort groups BY letters or numbers ). In addition, you cannot reference a domain with an alias in the group by clause. Group columns must be in the root data, but they do not need to appear in the result.
#3: limited data before grouping
You can add a WHERE clause to limit the data grouped by group. For example, the following statement only returns the ZIP code list of customers in the Kentucky region.
SELECT ZIP
FROM MERS
WHERE State = 'ky'
GROUP BY ZIP
Before the group by clause evaluates the data value, it is important to remember that the WHERE clause filters the data.
Like group by, WHERE does not support aggregate functions.
#4: return all groups
When you use WHERE to filter data, only the records you specified are displayed in the group. Data that meets the group definition but does not meet the clause conditions will not appear in the group. Regardless of the WHERE condition, if you want to include ALL data, add an ALL clause. For example, if you add an ALL clause in the preceding statement, ALL postal code groups are returned, not just those in the Kentucky region.
SELECT ZIP