The Wayback Machine - https://web.archive.org/web/20071011015005/http://sqlcourse2.com:80/having.html


Free Newsletters:
DatabaseJournal
DBANews
SQLCourse2
Advanced Online SQL Training		 Search Database Journal:
 
HOME News MS SQL Oracle DB2 Access MySQL PHP SQL Etc Scripts Links Forums DBA Talk
internet.com
SQL Courses
1 Start Here - Intro
2 SELECT Statement
3 Aggregate Functions
4 GROUP BY clause
5 HAVING clause
6 ORDER BY clause
7 Combining Conditions & Boolean Operators
8 IN and BETWEEN
9 Mathematical Functions
10 Table Joins, a must
11 SQL Interpreter
12 Advertise on SQLCourse.com
13 Other Tutorial Links
14 Technology Jobs


internet.commerce
Partner With Us
Cars
Business Gifts Canada
Blog
Televisions
Promotional Products
Corporate Awards
Free Credit Score
GPS
Car Donation
Laptop Computers
Memory Upgrades
Plasma Televisions
Custom Products
Promos and Premiums

internet.com
IT
Developer
Internet News
Small Business
Personal Technology
International

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers


CIO Insights from IBM: Build Collaboration to Drive Innovation. Beyond providing the right technology, CIOs can help ensure that collaboration initiatives are aligned with top-level business objectives. Learn more here.

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 clause syntax:


SELECT column1, 
SUM(column2)

FROM "list-of-tables"

GROUP BY "column-list"

HAVING "condition";

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;

Use these tables for the exercises
items_ordered
customers

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.

Click the HAVING exercise answers link below if you have any problems.

Answers to these Exercises

Enter SQL Statement here:

SQL Course 2 Curriculum
<<previous 1 2 3 4 5 6 7 8 9 10 11 12 13 14  next>>


Solutions


JupiterOnlineMedia

internet.comearthweb.comDevx.commediabistro.comGraphics.com

Search:

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info


Legal Notices, Licensing, Reprints, & Permissions, Privacy Policy.

Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers