Grouping and Sorting
Now that you’ve learned exactly what SQL is (in my previous column), and how to use the standard
SELECT functions, it’s time to move ahead to other features. In this section, I’ll be covering special grouping, logical and mathematical functions. These functions build upon what you’ve hopefully already learned, and allow you to leverage that to use SQL in your work.
SELECT statement allows you to look up data in your database, it’s
far more useful to be able to group and sort the resulting data. Without
grouping and sorting, you end up with globs of data, which while they may
be useful, are time-consuming to work with.
Grouping in a SELECT clause
Grouping in a SELECT clause is both obvious and simple. As an example,
imagine you have a SQL database of all the checks paid to vendors by your
accounts receivable department. Each record contains the payee, check
number, amount of payment, and any comments about the account. You are
trying, via a SQL statement, to find the total amount paid to each vendor.
Your basic query, which would find the payees, check numbers, and amounts
would look like this:
Which would obtain the following results:
Obviously, this isn’t what you need, as Smith Co. is duplicated in the
results, and you wanted their total payments grouped together. So, you
would modify the query as follows:
SELECT PAYEE ,SUM (PAY_AMOUNT)
GROUP BY PAYEE;
Which results in:
Notice the use of the
SUM is one of the more common SQL
functions you will use.
SUM does what its name implies, it totals up the
amounts you’ve told it to. As you can see from our example, the structure
SUM function is
"SUM"(Column to sum)
Taking the previous examples as a base, I’ll now introduce you to the
function. Just as
SUM works as its name implies,
COUNT does likewise. With
COUNT function, you can total up the number of references to a field.
Here’s a modification of the previous query that will illustrate:
GROUP BY PAYEE;
Our query produces the following results: