Simple SQL: Pt. 2

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.

While a `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:

``````SELECT PAYEE,CHECK_NUM,PAY_AMOUNT
FROM ACCOUNTS;``````

Which would obtain the following results:

 PAYEE CHECK_NUM PAY_AMOUNT Smith Co. 1125 1100.00 James Co. 1126 500.00 Tango Co. 1127 150.00 Smith Co. 1128 500.00

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)
FROM ACCOUNTS
GROUP BY PAYEE;``````

Which results in:

 PAYEE SUM Smith Co. 1600.00 James Co. 500.00 Tango Co. 150.00

Notice the use of the `SUM` function. `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
of a `SUM` function is

``"SUM"(Column to sum)``

Taking the previous examples as a base, I’ll now introduce you to the `COUNT`
function. Just as `SUM` works as its name implies, `COUNT` does likewise. With
the `COUNT` function, you can total up the number of references to a field.
Here’s a modification of the previous query that will illustrate:

``````SELECT PAYEE,SUM(PAY_AMOUNT),COUNT(PAYEE)
FROM ACCOUNTS
GROUP BY PAYEE;``````

Our query produces the following results:

 PAYEE SUM COUNT Smith Co. 1600.00 2 James Co. 500.00 1 Tango Co. 150.00 1