As you are aware, the job of aggregate functions is to summarize column data values stored in rows. For example, totals of a column, minimum/maximum value in a range of values, total number of rows present in a column etc. These aggregates are divided into two groups i.e. Math and Statistical. SUM, AVG, MIN, MAX, COUNT are all Math aggregates. Statistical aggregate functions are used to derive Kurtosis, Skew, Standard deviation, Variance etc. For this instance we would try on Math aggregates. First, create a volatile table to play with.

CREATE VOLATILE TABLE Salary

(Name CHAR (10), Salary INTEGER, Dept Integer)

ON COMMIT    PRESERVE ROWS;

Insert data into it as follows;

Insert Into salary Values    (’John’, 5000,110);

Insert Into salary Values    (’Tom’, 3000, 110);

Insert Into salary Values    (’Larry’, 1000, 210);

Insert Into salary Values    (’Kate’, 2000, 210);

Insert Into salary Values    (’Lisa’, 5400, 310);

Insert Into salary Values    (’James’, 15000, 310);

Insert Into salary Values   (’Lisa’, 5400, 100);

Insert Into salary Values   (’James’, 15000, 100);

Let’s use all the Math aggregate functions;

Select Sum(salary) As “Total Salary”,

          AVG(salary) As “Average Salary”,

            MIN(salary) As “Minimum Salary”,

            MAX(salary) As “Maximum Salary”,

            Count(salary) As “No of People with Salary”

From   salary 

If you run the above query in SQL Assistant, you would get the following output;

Total Salary Average Salary Minimum Salary Maximum Salary No of People with Salary

51,800        6,475.00               1,000                15,000                8

The GROUP BY clause can be very handy in case there is a non-aggregate (name column in case) column present and needed to be included in the result set. Here is the revised query; Select dept,

Sum(salary) As “Total Salary”,

          AVG(salary) As “Average Salary”,

            MIN(salary) As “Minimum Salary”,

            MAX(salary) As “Maximum Salary”,

            Count(salary) As “No of People with Salary”

From   salary

Group By Dept

And the result set would be as follows; 

Dept Total Salary Average Salary Minimum Salary Maximum Salary No of People with Salary

210      3,000               1,500.00          1,000               2,000               2

310      20,400             10,200.00        5,400               15,000             2

110      8,000               4,000.00          3,000               5,000               2

100      20,400             10,200.00        5,400               15,000             2

If there is a need to limit the rows based on any derived amount like average salary, this can be done by using the HAVING clause as follows; Select dept,

Sum(salary) As “Total Salary”,

          AVG(salary) As “Average Salary”,

            MIN(salary) As “Minimum Salary”,

            MAX(salary) As “Maximum Salary”,

            Count(salary) As “No of People with Salary”

From   salary

HAVING “Average Salary” > 10000

Group By Dept

And it would display results as below;

Dept Total Salary Average Salary Minimum Salary Maximum Salary No of People with Salary

310      20,400             10,200.00        5,400               15,000             2

100      20,400             10,200.00        5,400               15,000             2

One point to note, when you use any of these aggregate functions, the value NULL is ignored for calculations.

Popularity: 33% [?]

Our Random Articles

• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress
• Usage of Macro in Teradata

More Links