SQL 101 6: DISTINCT & Functions

This is the sixth in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The DISTINCT Option
This option removes duplicate rows from the Results Table. The result of the query will only include each distinct selected set of Columns, even though there may be many more such occurrences in the Table.   NOTE:  Only rows where ALL columns are the same will be removed.

One example of use is where you wish to query the stock file to see if you have ever sold a particular product, you only wish a row to be returned as confirmation, you do not need a row for every occurrence of the sold stock record. This query would use the DISTINCT option to return only the required data.

Calculated Columns
The value of a Column can have a calculation applied to it. This calculation can be in the form of a numeric calculation or a calculation based on the value of another Column.

Functions
It is possible that instead of displaying the contents of a Column in the Results Table, you can display the result of performing certain operations on the Column contents. The functions available are as follows:

AVG(COLUMN)
This function calculates the average value of the specified Column. The name of the Column to be averaged must be enclosed in parentheses.

SUM(COLUMN)
This function operates exactly as the average function, but produces the SUM of the selected Columns.

MIN(COLUMN)
This function operates exactly as the average function, but produces the minimum of the selected Columns.

MAX(COLUMN)
This function operates exactly as the average function, but produces the maximum of the selected Columns.

COUNT(*)
This function operates exactly as the average function, but produces a COUNT of the number of Columns that have met the selected criteria.

Next: SQL Background Information

"If you really do put a small value upon yourself, rest assured that the world will not raise your price." - Anonymous