Aggregate Functions

Aggregate Functions

MIN returns the smallest value in a given column
MAX returns the largest value in a given column
SUM returns the sum of the numeric values in a given column
AVG returns the average value of a given column
COUNT returns the total number of values in a given column
COUNT(*) returns the number of rows in a table

Aggregate functions are used to compute against a “returned column of numeric data” from your SELECT statement. They basically summarize the results of a particular column of selected data. We are covering these here since they are required by the next topic, “GROUP BY”. Although they are required for the “GROUP BY” clause, these functions can be used without the “GROUP BY” clause. For example:

 
SELECT AVG(salary)




FROM employee;

This statement will return a single result which contains the average value of everything returned in the salary column from the employee table.

Another example:

 
SELECT AVG(salary)




FROM employee;




WHERE title = 'Programmer';

This statement will return the average salary for all employees whose title is equal to ‘Programmer’

Example:

 
SELECT Count(*)




FROM employees;

This particular statement is slightly different from the other aggregate functions since there isn’t a column supplied to the count function. This statement will return the number of rows in the employees table.

Review Exercises

  1. Select the maximum price of any item ordered in the items_ordered table. Hint: Select the maximum price only.>
  2. Select the average price of all of the items ordered that were purchased in the month of Dec.
  3. What are the total number of rows in the items_ordered table?
  4. For all of the tents that were ordered in the items_ordered table, what is the price of the lowest tent? Hint: Your query should return the price only.

GROUP BY clause

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:

Let’s say you would like to retrieve a list of the highest paid salaries in each dept:

 
SELECT max(salary), dept




FROM employee 




GROUP BY dept;

This statement will select the maximum salary for the people in each unique department. Basically, the salary for the person who makes the most in each department will be displayed. Their, salary and their department will be returned.

Review Exercises

  1. How many people are in each unique state in the customers table? Select the state and display the number of people in each. Hint: count is used to count rows in a column, sum works on numeric data only.
  2. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.
  3. How many orders did each customer make? Use the items ordered table. Select the customerid, number of orders they made, and the sum of their orders. Click the Group By answers link below if you have any problems.

HAVING clause

The HAVING clause allows you to specify conditions on the rows for each group – in other words, which rows should be selected will be based on the conditions you specify. The HAVING clause should follow the GROUP BY clause if you are going to use it.

HAVING can best be described by example. Let’s say you have an employee table containing the employee’s name, department, salary, and age. If you would like to select the average salary for each employee in each department, you could enter:

 
SELECT dept, avg(salary)




FROM employee




GROUP BY dept;

But, let’s say that you want to ONLY calculate & display the average if their salary is over 20000:

 
SELECT dept, avg(salary)




FROM employee




GROUP BY dept




HAVING avg(salary) > 20000;

Review Exercises (note: yes, they are similar to the group by exercises, but these contain the HAVING clause requirements

  1. How many people are in each unique state in the customers table that have more than one person in the state? Select the state and display the number of how many people are in each if it’s greater than 1.
  2. From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Only display the results if the maximum price for one of the items is greater than 190.00.
  3. How many orders did each customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders if they purchased more than 1 item.

ORDER BY clause

ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by.

 
ASC = Ascending Order - default




DESC = Descending Order

For example:

SELECT employee_id, dept, name, age, salary

FROM employee_info

WHERE dept = ‘Sales’

ORDER BY salary;

If you would like to order based on multiple columns, you must seperate the columns with commas. For example:

 
SELECT employee_id, dept, name, age, salary




FROM employee_info




WHERE dept = 'Sales'




ORDER BY salary, age DESC;

Review Exercises

  1. Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.
  2. Same thing as exercise #1, but display the results in Descending order.
  3. Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.

Combining conditions and Boolean Operators

The AND operator can be used to join two or more conditions in the WHERE clause. Both sides of the AND condition must be true in order for the condition to be met and for those rows to be displayed.

the OR operator can be used to join two or more conditions in the WHERE clause also. However, either side of the OR operator can be true and the condition will be met – hence, the rows will be displayed. With the OR operator, either side can be true or both sides can be true.

For example:

 
SELECT employeeid, firstname, lastname, title, salary




FROM employee_info




WHERE salary >= 50000.00 AND title = 'Programmer';

This statement will select the employeeid, firstname, lastname, title, and salary from the employee_info table where the salary is greater than or equal to 50000.00 AND the title is equal to ‘Programmer’. Both of these conditions must be true in order for the rows to be returned in the query. If either is false, then it will not be displayed.

Although they are not required, you can use paranthesis around your conditional expressions to make it easier to read:

 
SELECT employeeid, firstname, lastname, title, salary




FROM employee_info




WHERE (salary >= 50000.00) AND (title = 'Programmer');

Another Example:

SELECT firstname, lastname, title, salary

FROM employee_info

WHERE (title = ‘Sales’) OR (title = ‘Programmer’);

This statement will select the firstname, lastname, title, and salary from the employee_info table where the title is either equal to ‘Sales’ OR the title is equal to ‘Programmer’.

Review Exercises

  1. Select the customerid, order_date, and item from the items_ordered table for all items unless they are ‘Snow Shoes’ or if they are ‘Ear Muffs’. Display the rows as long as they are not either of these two items.
  2. Select the item and price of all items that start with the letters ‘S’, ‘P’, or ‘F’.

IN and BETWEEN Conditional Operators

The IN conditional operator is really a set membership test operator. That is, it is used to test whether or not a value (stated before the keyword IN) is “in” the list of values provided after the keyword IN.

For example:

 
SELECT employeeid, lastname, salary




FROM employee_info




WHERE lastname IN ('Hernandez', 'Jones', 'Roberts', 'Ruiz');

This statement will select the employeeid, lastname, salary from the employee_info table where the lastname is equal to either: Hernandez, Jones, Roberts, or Ruiz. It will return the rows if it is ANY of these values.

The IN conditional operator can be rewritten by using compound conditions using the equals operator and combining it with OR – with exact same output results:

 
SELECT employeeid, lastname, salary




FROM employee_info




WHERE lastname = 'Hernandez' OR lastname = 'Jones' OR lastname = 'Roberts'
OR lastname = 'Ruiz';

As you can see, the IN operator is much shorter and easier to read when you are testing for more than two or three values.

You can also use NOT IN to exclude the rows in your list.

The BETWEEN conditional operator is used to test to see whether or not a value (stated before the keyword BETWEEN) is “between” the two values stated after the keyword BETWEEN.

For example:

 
SELECT employeeid, age, lastname, salary




FROM employee_info

 
WHERE age BETWEEN 30 AND 40;

This statement will select the employeeid, age, lastname, and salary from the employee_info table where the age is between 30 and 40 (including 30 and 40).

This statement can also be rewritten without the BETWEEN operator:

 
SELECT employeeid, age, lastname, salary




FROM employee_info




WHERE age >= 30 AND age <= 40;

Review Exercises

  1. Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.
  2. Select the firstname, city, and state from the customers table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.

Disclaimer:

The information contains in this web-site is prepared for educational purpose. This site may be used by the students, faculties, independent learners and the learned advocates of all over the world. Researchers all over the world have the access to upload their writes up in this site. In consideration of the people’s participation in the Web Page, the individual, group, organization, business, spectator, or other, does hereby release and forever discharge the Lawyers & Jurists, and its officers, board, and employees, jointly and severally from any and all actions, causes of actions, claims and demands for, upon or by reason of any damage, loss or injury, which hereafter may be sustained by participating their work in the Web Page. This release extends and applies to, and also covers and includes, all unknown, unforeseen, unanticipated and unsuspected injuries, damages, loss and liability and the consequences thereof, as well as those now disclosed and known to exist.  The provisions of any state’s law providing substance that releases shall not extend to claims, demands, injuries, or damages which are known or unsuspected to exist at this time, to the person executing such release, are hereby expressly waived. However the Lawyers & Jurists makes no warranty expressed or implied or assumes any legal liability or responsibility for the accuracy, completeness or usefulness of any information, apparatus, product or process disclosed or represents that its use would not infringe privately owned rights. Reference herein to any specific commercial product process or service by trade name, trade mark, manufacturer or otherwise, does not necessarily constitute or imply its endorsement, recommendation or favouring by the Lawyers & Jurists. The views and opinions of the authors expressed in the Web site do not necessarily state or reflect those of the Lawyers & Jurists. Above all, if there is any complaint drop by any independent user to the admin for any contents of this site, the Lawyers & Jurists would remove this immediately from its site.