You need to understand how to use some SQL clauses and operators to schedule SQL data for efficient analysis. The following suggestions tell you how to build statements to get the results you want.
Arranging data in a meaningful way can be a challenge. Sometimes you just need to do a simple classification. Usually you have to do more processing-grouping to facilitate analysis and totals. Fortunately, SQL provides a large number of clauses and operators for classification, grouping, and totals. The following recommendations will help you understand when to categorize, when to group, when, and how to make a grand total. For more information about each clause and operator, see the online books.
#1: Sorting by Category
In general, we do need to sort all the data. The SQL ORDER BY clause arranges the data in alphabetical or numeric order. As a result, homogeneous data is clearly categorized into individual groups. However, these groups are only the result of classification, and they are not really groups. ORDER by displays each record, and one group may represent multiple records.
#2: Reduce similar data in a group
The biggest difference between classification and grouping is that categorical data displays all records (within any qualifying criteria), while grouped data does not display these records. The GROUP by clause reduces similar data in one record. For example, GROUP by can return a unique list of postal codes from the source file that duplicates those values:
SELECT ZIP
From Customers
GROUP by ZIP
Include only those columns in the group by and select column lists. In other words, the select list must match the group list. There is only one case exception: The select list can contain aggregate functions. (The group by does not support aggregation functions.) ）
Remember, group by does not categorize groups that are produced as results. To sort the groups alphabetically or numerically, add an ORDER BY clause (#1). In addition, you cannot refer to an aliased domain in the GROUP BY clause. The group columns must be in the underlying data, but they do not have to appear in the results.
#3: Qualifying data before grouping
You can add a WHERE clause to qualify the data grouped by the group by. For example, the following statement returns only a list of ZIP codes for customers in the Kentucky region.
SELECT ZIP
From Customers
WHERE state = ' KY '
GROUP by ZIP
It is important to keep in mind that the where data is filtered before the GROUP BY clause evaluates the value of the data.
As with group by, where does not support aggregation functions.
#4: Return all Groups
When you use where to filter data, the resulting group displays only those records that you specify. Data that conforms to the group definition but does not meet the clause criteria will not appear in the group. If you want to include all of the data, add an all clause regardless of the where condition. For example, adding an all clause in the preceding statement returns all the postal code groups, not just the Kentucky region.
SELECT ZIP
http://www.bkjia.com/PHPjc/630965.html www.bkjia.com true http://www.bkjia.com/PHPjc/630965.html techarticle you need to understand how to use some SQL clauses and operators to schedule SQL data for efficient analysis. The following suggestions tell you how to build statements to get the results you want. ...
