Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

Since we have started dealing with numbers, the next natural question to ask is if it is possible to do math on those numbers, such as summing them up or taking their average. The answer is yes! SQL has several arithematic functions, and they are:

·    AVG
·    COUNT
·    MAX
·    MIN
·    SUM

The syntax for using functions is,

SELECT "function type"("column_name")
FROM "table_name"

For example, if we want to get the sum of all sales from the following table,

Table Store_Information

we would type in

SELECT SUM(Sales) FROM Store_Information

Result:

$2750 represents the sum of all Sales entries: $1500 + $250 + $300 + $700.

In addition to using functions, it is also possible to use SQL to perform simple tasks such as addition (+) and subtraction (-). For character-type data, there are also several string functions available, such as concatenation, trim, and substring functions. Different RDBMS vendors have different string functions implementations, and it is best to consult the references for your RDBMS to see how these functions are used.

 SQL COUNT 

Another arithmetic function is COUNT. This allows us to COUNT up the number of row in a certain table. The syntax is,

SELECT COUNT("column_name")
FROM "table_name"

For example, if we want to find the number of store entries in our table,

Table Store_Information

we'd key in

SELECT COUNT(store_name)
FROM Store_Information

Result:

COUNT and DISTINCT can be used together in a statement to fetch the number of distinct entries in a table. For example, if we want to find out the number of distinct stores, we'd type,

SELECT COUNT(DISTINCT store_name)
FROM Store_Information

Result:

 SQL GROUP BY

Now we return to the aggregate functions. Remember we used the SUM keyword to calculate the total sales for all stores? What if we want to calculate the total sales for each store? Well, we need to do two things: First, we need to make sure we select the store name as well as total sales. Second, we need to make sure that all the sales figures are grouped by stores. The corresponding SQL syntax is,

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"

Let's illustrate using the following table,

Table Store_Information

We want to find total sales for each store. To do so, we would key in,

SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name

Result:

The GROUP BY keyword is used when we are selecting multiple columns from a table (or tables) and at least one arithmetic operator appears in the SELECT statement. When that happens, we need to GROUP BY all the other selected columns, i.e., all columns except the one(s) operated on by the arithmetic operator.

 SQL HAVING 

Another thing people may want to do is to limit the output based on the corresponding sum (or any other aggregate functions). For example, we might want to see only the stores with sales over $1,500. Instead of using the WHERE clause in the SQL statement, though, we need to use the HAVING clause, which is reserved for aggregate functions. The HAVING clause is typically placed near the end of the SQL statement, and a SQL statement with the HAVING clause may or may not include the GROUP BY clause. The syntax for HAVING is,

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)

Note: the GROUP BY clause is optional.

In our example, table Store_Information,

Table Store_Information

we would type,

SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

Result: