Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 1, 2000

Simple SQL: Pt. 2

By Ted Brockwood

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


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date