Online College Courses for Credit

+
Filters to Specify Data

Filters to Specify Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that is able to return specific results from a fairly large table of records spanning multiple tables containing wide-ranging text-based data (such as comments) using LIKE.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using various filters to return specific results using different options with the WHERE and HAVING clauses in two parts:

  1. Understanding the Complexity
  2. Stepping Through an Example

1. Understanding the Complexity

The complexity of the SELECT statements can increase based on the criteria being asked in which we will have to make use of both the WHERE and HAVING clauses with additional filters. Although we have already looked at the WHERE as the filter for individual rows and the HAVING is for groups of rows.

Anything that we have in the aggregate function results is items that we can use in the HAVING clause. As an example, if we were asked to get all of the invoices and cost from the invoice_line table of those that had the cost is greater than 1 ordered by the invoice_id, the query would look like the following:

SELECT invoice_id, SUM(unit_price * quantity)
FROM invoice_line
GROUP BY invoice_id
HAVING SUM(unit_price * quantity) > 1
ORDER BY invoice_id;

Remember that the WHERE clause sees one row at a time, so we would not be able to evaluate the SUM across all of the invoice_id values. The HAVING clause is executed after the groups have been created.

File:11329-1380-1.png

If we were asked to expand on this to find invoice_id values that were greater than 100, we could add this to the HAVING clause to act similar to the WHERE clause:

SELECT invoice_id, SUM(unit_price * quantity)
FROM invoice_line
GROUP BY invoice_id
HAVING SUM(unit_price * quantity) > 1
AND invoice_id > 100 ORDER BY invoice_id;

File:11330-1380-2.png

The reason we could do this is due to the fact that the invoice_id is part of the GROUP BY clause. However, if we needed to filter based on another column like the unit_price to check if it was more than 1 before we grouped them and added it to the HAVING clause:

SELECT invoice_id, SUM(unit_price * quantity)
FROM invoice_line
GROUP BY invoice_id
HAVING SUM(unit_price * quantity) > 1
AND unit_price > 1
ORDER BY invoice_id;

We would have an error generated:

File:11331-1380-3.png

This is due to the fact the unit_price column is not part o the GROUP BY field nor a result of an aggregate function. To be valid in the HAVING clause, we can only compare the aggregate functions or the column part of the GROUP BY. For it to be a valid query, the check on the unit_price needs to be moved to the WHERE clause:

SELECT invoice_id, SUM(unit_price * quantity)
FROM invoice_line
WHERE unit_price > 1
GROUP BY invoice_id
HAVING SUM(unit_price * quantity) > 1
ORDER BY invoice_id;

We should see that the results look quite different:

File:11332-1380-4.png

This is because we are filtering out the rows that have the unit_price being greater than 1 before we combine each into groups.


2. Stepping Through an Example

Let us look at another scenario where we are looking for invoices for a set of customers (customer_id between 20 and 30) that having their billing country in the USA. We want to find those that have had at least one invoice that has a total is larger than 15. We want to also get the total amount they have ordered at all times. This may seem like a very complex query, but we will want to break the query down first.

First, we know that we are looking for data using the invoice table.

SELECT *
FROM invoice;

Exploring this data, we know that we want to look at a specific set of customers. We can identify two criteria without aggregate conditions that we can add to a WHERE clause:

SELECT *
FROM invoice
WHERE billing_country = 'USA'
AND customer_id BETWEEN 20 AND 30;

This gives us 63 records that fit these criteria:

File:11333-1380-5.png

However, we need to only look for the customer_id to be returned along with the SUM of the total and the MAX of the total. The customer_id should be what we are grouping by as well. This would change our SELECT statement to look like:

SELECT customer_id, SUM(total),MAX(total)
FROM invoice
WHERE billing_country = 'USA'
AND customer_id BETWEEN 20 AND 30
GROUP BY customer_id;

File:11334-1380-6.png

The next step that we have to look at is to find the groups that have the maximum of the total being greater than 15. As this is looking at an aggregate function, it has to go into the HAVING clause.

SELECT customer_id, SUM(total),MAX(total)
FROM invoice
WHERE billing_country = 'USA'
AND customer_id BETWEEN 20 AND 30
GROUP BY customer_id
HAVING MAX(total) > 15;

File:11335-1380-7.png

In looking at the query, one thing to note is that the customer_id is in the GROUP BY clause so the comparison for the customer_id could have also appeared in the HAVING clause as well like:

SELECT customer_id, SUM(total),MAX(total)
FROM invoice
WHERE billing_country = 'USA'
GROUP BY customer_id
HAVING MAX(total) > 15
AND customer_id BETWEEN 20 AND 30;

This would result in the same result set:

File:11336-1380-8.png


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
Building a complex statement with various filters for rows and groups should be built on one step at a time.