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:

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:

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: