SQL HAVING

11 Key excerpts on "SQL HAVING"

• 

  eBook - PDF

  Beginning SQL

  • Paul Wilton, John Colby(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  Using the HAVING Clause with GROUP BY Statements The HAVING clause enables you to specify conditions that filter which group results appear in the final results. In some ways, the HAVING clause resembles a WHERE clause for groups, in that you place it 202 Chapter 6 immediately after the GROUP BY statement. For example, the following SQL creates a list of cities that have three or more members in them: SELECT City FROM MemberDetails GROUP BY City HAVING COUNT(MemberId) >= 3; The HAVING clause applies on a per-group basis, filtering out those groups that don’t match the condi- tion, whereas the WHERE clause applies on a per-record basis, filtering out records. The results for the preceding SQL are shown in the following table: City Orange Town If you take away the HAVING clause and add details of the COUNT(MemberId), your SQL is as follows: SELECT City, COUNT(MemberId) FROM MemberDetails GROUP BY City; You can see how the database gets its results: City COUNT(MemberId) NULL 1 Big City 2 Dover 1 New Town 1 Orange Town 4 Townsville 2 Windy Village 2 You can see from the list that only Big City and New Town have three or more members. It’s important to note that the WHERE clause restricts the record set that the GROUP BY clause works with. However, the HAVING clause affects only the displayed final results. Now that you’re acquainted with the HAVING clause’s functions, the following Try It Out shows you how to use it in SQL statements. Try It Out Working with the HAVING Clause 1. In an earlier example, the film club’s chairperson wanted to know how many members listed each film category as their favorite. The chairperson also wanted the list ordered from the most popular to the least popular category. However, now the chairperson wants the list to include only those categories where four or more members listed that category as their favorite. Execute the following SQL to answer the chairperson’s question: 203 Grouping and Aggregating Data

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  Hands-On Data Science with SQL Server 2017

  Perform end-to-end data analysis to gain efficient data insight

  • Marek Chmel, Vladimír Mužný(Authors)
  • 2018(Publication Date)
  • Packt Publishing

    (Publisher)

  . Let's explore results from the previous section. Here, we had a result from the following query: SELECT CategoryName , SubcategoryName , COUNT(*) as RecordCountFROM #srcGROUP BY CategoryName, SubcategoryName

  The result from the preceding query has 37 rows containing RecordCount values from 1 to 43. Let's say that subcategories with fewer than five products are not significant for us. We then have two options of how to filter out subcategories with a small amount of products. The first of these options is shown in the following query:

  ; WITH cte AS(SELECT CategoryName , SubcategoryName , COUNT(*) as RecordCountFROM #srcGROUP BY CategoryName, SubcategoryName)SELECT * FROM cte WHERE RecordCount >= 5

  This query uses CTE to calculate aggregations, and when the result of the CTE query is made, it is filtered using the WHERE clause of the query and the CTE. The preceding query works, but using the HAVING clause makes the task at least more readable. Let's look at the following query:

  SELECT CategoryName , SubcategoryName , COUNT(*) as RecordCountFROM #srcGROUP BY CategoryName, SubcategoryNameHAVING COUNT(*) >= 5 The preceding query does not use CTE, but it produces exactly the same result. From the preceding sample query we also see that the HAVING clause, if it's present, always follows the GROUP BY clause.

  Now we have two clauses that are useful for data filtering, so let's explain what the difference is between both clauses. The WHERE clause filters data before it's aggregated; it impacts the amount of records coming into the aggregation. The HAVING clause filters data after it's aggregated. You may wonder whether we can add an aggregate function as part of a predicate into the WHERE clause. However, we must not do this because the result of the aggregate function is not known when the WHERE clause filters rows. Can we add a not aggregated column into the HAVING clause? Yes, we can, but it reduces the readability of our queries. Let's explore this in the following example. In this example, we don't want to aggregate data for the Clothing category; the following queries will do this for us:

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Oracle Data Warehouse Tuning for 10g

  • Gavin JT Powell(Author)
  • 2011(Publication Date)
  • Digital Press

    (Publisher)

  How to Use the HAVING Clause The only thing to remember about the HAVING clause is that it applies to the aggregated results returned by the GROUP BY clause. In other words, the HAVING clause is used to filter aggregated rows. A common mistake is to use HAVING in place of a WHERE clause, or vice versa. The WHERE clause filters rows at the source; in other words, it can potentially decrease I/O activity by reading fewer rows. Reading fewer rows is always good for perfor-mance. The HAVING clause will be applied to aggregated results produced by a GROUP BY clause. In other words, the HAVING clause is executed after all I/O activity is completed. Therefore, replacing WHERE clause filter-ing with HAVING clause filtering can hurt performance, since I/O activity could be increased. Figure 7.10 shows the inappropriate use of the HAVING clause replaced with a more appropriate WHERE clause, clearly showing the resulting improvement in performance. Figure 7.10 Do not use a HAVING clause to replace a WHERE clause. 170 7.1 Basic Query Tuning Figure 7.11 shows an appropriate use of the HAVING clause, such that the HAVING clause filters the result of the GROUP BY clause, the aggre-gation, and the expression SUM(SALE_ID). Also note the significant decrease in cost between Figures 7.10 and 7.11, where Figure 7.11 has a much smaller amount in the Bytes column, thus much less I/O activity. The HAVING clause filter can help performance because it filters. Fil-tering allows the processing and return of fewer rows. The HAVING clause filtering, shown in the query plans in Figures 7.10 and 7.11, shows that HAVING clause filtering is always executed after the GROUP BY sorting process. In general, the GROUP BY clause can perform some sorting if it matches indexing. Filtering aggregate results with the HAVING clause can help increase performance by filtering aggregated results of the GROUP BY clause.

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  SQL Pocket Primer

  • Oswald Campesato(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  city name:

  SELECT city, COUNT(city) FROM weather GROUP BY city ORDER BY city; +------+-------------+ | city | count(city) | +------+-------------+ |      |           2 | | chi  |           1 | | se   |           1 | | sf   |           7 | +------+-------------+ 4 rows in set (0.003 sec)

  The HAVING clause enables you to specify an additional filter condition for the result set. For example, the following SQL statement extends the previous SQL statement by restricting the result set to cities whose count is greater than 2 in the weather table:

  SELECT city, COUNT(city) FROM weather GROUP BY city HAVING count(*) > 2 ORDER BY city; +------+-------------+ | city | count(city) | +------+-------------+ | sf   |           7 | +------+-------------+ 1 row in set (0.003 sec)

  A HAVING clause and a WHERE clause both filter the data in a result set, but there is a difference. HAVING applies only to groups of data, whereas the WHERE clause applies to individual rows.

  The HAVING clause is executed before the SELECT statement, which means that you cannot use aliases of aggregated columns in the HAVING clause.

  Displaying Duplicate Attribute Values

  In a previous section, you learned how to delete duplicate rows, where two rows are considered duplicates if they have the same attribute value. The following SQL statement uses the HAVING

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL for Data Analytics

  Harness the power of SQL to extract insights from data, 3rd Edition

  • Jun Shan, Matt Goldwasser, Upom Malik, Benjamin Johnston(Authors)
  • 2022(Publication Date)
  • Packt Publishing

    (Publisher)

  0.5 is that the median is the 50th percentile, which is 0.5 as a fraction. This gives you the following result:

  Figure 4.22: Result of an ordered set aggregate function

  With ordered set aggregate functions, you now have the tools for calculating virtually any aggregate statistic of interest for a dataset. In the next section, you will learn how to use aggregates to deal with data quality.

  Aggregate Functions with the HAVING Clause

  You learned about the WHERE clause in this chapter when you worked on SELECT statements, which select only certain rows meeting the condition from the original table for later queries. You also learned how to use aggregate functions with the WHERE clause in the previous section. Bear in mind that the WHERE clause will always be applied to the original dataset. This behavior is defined by the SQL SELECT statement syntax, regardless of whether there is a GROUP BY clause or not. Meanwhile, GROUP BY is a two-step process. In the first step, SQL selects rows from the original table or table set to form aggregate groups. In the second step, SQL calculates the aggregate function results. When you apply a WHERE clause, its conditions are applied to the original table or table set, which means it will always be applied in the first step. Sometimes, you are only interested in certain rows in the aggregate function result with certain characteristics, and only want to keep them in the query output and remove the rest. This can only happen after the aggregation has been completed and you get the results, thus it is part of the second step of GROUP BY

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  There can be multiple group- ing attributes as illustrated by the query sql_CourseOfferingCount that computes the count of the number of employees who took a course on a date. The count computes the number of tuples in the grouping that includes both cID and tDate. As a generalization with grouping, the select clause contains the grouping attributes specified in the group by clause and the columns repre- senting the desired aggregation for that group. sql_CourseOfferingCount: √ select T.cID, T.tDate, count(*) as emptookoffering from takes T group by T.cID, T.tDate This introductory text follows the semantics for grouping in the SQL standard, requiring that the non-aggregate columns in the select clause must appear in the group by clause as the only grouping attributes. This requirement makes sense because the query is asking for an aggregate result for the same values of those grouping attributes. A having clause specifies a filtering condition on the result of the grouping. SQL also supports the ability to place a selection condition on the results of grouping using the having clause. Essentially a having clause is specifying a filtering, similar to a where clause, for the group. What if you only wanted the titles with the count of the number of employees who took a course such that there are at least four such employees with that title. This query can be answered by appending the following having clause to sql_CountByTitleTookCourses: having emptookcoursescount >= 4 Note that some database products do not recognize the renaming of the attribute within the having clause. In that case, the aggregate operator must be used again: having count(distinct T.eID) >= 4 Note that the having clause is just a shortcut, allowing the use of one query instead of using a sec- ond query to filter the result of the grouping. Also, the query can be rewritten with the aggregation query specified as an inline view using a where to filter the result.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Management Systems

  A Business-Oriented Approach Using ORACLE, MySQL and MS Access

  • Sotirios Zygiaris(Author)
  • 2018(Publication Date)
  • Emerald Publishing Limited

    (Publisher)

  without the HAVING clause, the extracted table will return all rows without any selection taking place on the extracted table.

  • SELECT SI.SALE_ID, COUNT(*) AS TOTAL_OF_ITEMS, SUM(SI.SLITEM_UNITS) AS TOTAL_QUANTITY
  • FROM SALE_ITEM SI GROUP BY SI.SALE_ID;

  In the HAVING clause, all conditional and logical operators apply, for example:

  How many items does each sale have? Give a list with sale items of more than three and the total quantity of product sold of more than seven per sale.

  • SELECT SI.SALE_ID, COUNT(*) AS TOTAL_OF_ITEMS, SUM(SI.SLITEM_UNITS) AS TOTAL_QUANTITY
  • FROM SALE_ITEM SI GROUP BY SI.SALE_ID HAVING COUNT(*) > 3 AND SUM(SI.SLITEM_UNITS) > 7;

  Since aggregate functions return numbers, the conditional and logical operators may be applied to numbers too.

  SQL commands with HAVING clause may be used in conjunction with WHERE clause. The WHERE clause limits the rows extracted from the source table and the HAVING clause limits the rows extracted from the aggregated table.

  How many items does each sale have for 2016? Give a list with sale items having more than three per sale.

  • SELECT SI.SALE_ID, COUNT(*) AS TOTAL_OF_ITEMS, SUM(SI.SLITEM_UNITS) AS TOTAL_QUANTITY
  • FROM SALE_ITEM SI JOIN SALE SL ON SI.SALE_ID = SL.SALE_ID WHERE SL.SALE_DATE BETWEEN '01-01-2016' AND '12-31-2016' GROUP BY SI.SALE_ID HAVING COUNT(*) > 3;

  The WHERE clause limits the rows extracted from the joined source table. Only the sales in 2016 are grouped using the WHERE SL.SALE_DATE BETWEEN ‘01-01-2016' AND '12-31-2016' clause. At the end, the query extracts only the 2016 sales with sale items having more than three per sale.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  Avg somewhere in your query, typically in the SELECT clause. Without any aggregation functions, GROUP BY will either cause an error message, have no effect, or operate in the same way as SELECT DISTINCT, which is simpler to use.

  HAVING

  Suppose we are interested in performing the same query, but we want to see only customers who have spent a total of at least $7,000. This restriction cannot be imposed by a WHERE clause, because WHERE restrictions are always applied before grouping and aggregation occur. Here, we need to instead apply a criterion to the result of the Sum aggregation function, which can only be known after the grouping and aggregation steps. To apply criteria after grouping and aggregation, SQL provides an additional clause called HAVING. To implement this query, we add the clause

  HAVING Sum(UnitPrice*Quantity) >= 7000

  to the query, resulting in:

  SELECT FirstName, LastName, Sum(UnitPrice*Quantity) AS Revenue FROM CUSTOMER, ORDERS, ORDERDETAIL, PRODUCT WHERE CUSTOMER.CustomerID = Orders.CustomerID AND Orders.OrderID = ORDERDETAIL.OrderID AND ORDERDETAIL.ProductID = PRODUCT.ProductID GROUP BY CUSTOMER.CustomerID, FirstName, LastName HAVING Sum(UnitPrice*Quantity) >= 7000;

  This clause specifies that, after grouping and aggregation, we retain only output rows for which the sum of the unit price multiplied by quantity is at least 7,000. Now, only “big spenders” appear in the output, as shown in Table 10.9 .

  Table 10.9

  Output of query identifying high‐spending customers.

  First Name	Last Name	Revenue
  Benjamin	Masterson	$7,293.60
  Mary	Milgrom	$23,783.75
  Geoffrey	Hammer	$8,670.27
  Ashley	Flannery	$8,246.50

  HAVING and WHERE perform similar functions, but WHERE filters records before aggregation and HAVING filters records after

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  Data Science Fundamentals Pocket Primer

  • Oswald Campesato(Author)
  • 2021(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  GROUP BY in a SQL statement to display the number of purchase orders that were created on a daily basis:

  SELECT purchase_date, COUNT(*) FROM purchase_orders GROUP BY purchase_date;

  Select Statements with a HAVING Clause

  The following SQL statement illustrates how to specify GROUP BY in a SQL statement to display the number of purchase orders that were created on a daily basis, and only those days where at least 4 purchase orders were created:

  SELECT purchase_date, COUNT(*) FROM purchase_orders GROUP BY purchase_date; HAVING COUNT(purchase_date) > 3;

  WORKING WITH INDEXES IN SQL

  SQL enables you to define one or more indexes for a table, which can greatly reduce the amount of time that is required to select a single row or a subset of rows from a table.

  A SQL index on a table consists of one or more attributes in a table. SQL updates in a table that has one or more indexes requires more time than updates without the existence of indexes on that table because both the table and the index (or indexes) must be updated. Therefore, it’s better to create indexes on tables that involve table columns that are frequently searched.

  Here are two examples of creating indexes on the customers table:

  CREATE INDEX idx_cust_lname ON customers (lname);   CREATE INDEX idx_cust_lname_fname ON customers (lname,fname);

  WHAT ARE KEYS IN AN RDBMS?

  A key

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Concepts of Database Management

  • Joy Starks, Philip Pratt, Mary Last, , Joy Starks, Philip Pratt, Mary Last(Authors)
  • 2018(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  In other words, you want to display only those groups for which AVG(Balance) is less than $4,000. This restriction does not apply to individual rows but to groups . Because the WHERE clause applies only to rows, it is not the appropriate clause to accomplish the kind of selection you need. Fortunately, the HAVING clause is to groups as the WHERE clause is to rows (Figure 3-45). Only groups with an average balance less than $4,000 will be included HAVING clause FIGURE 3-45 SQL query to restrict the groups that are included 107 The Relational Model 2: SQL Q & A 3-7 Question: How is the SELECT clause used when grouping in SQL? Answer: The SELECT clause only can contain aggregate functions or fields that are then listed in the GROUP BY clause. The query results appear in Figure 3-46. In this case, the row created for a group will be displayed only when the average client balance is less than $4,000. Records grouped by consultants who have an average customer balance of less than $4,000 FIGURE 3-46 Query results You can include both a WHERE clause and a HAVING clause in the same query design, as shown in Figure 3-47. In this example, the condition in the WHERE clause restricts the rows from the Client table to those rows on which the credit limit is less than $10,000. These rows are grouped by consultant number. The HAVING clause then restricts the groups to those for which the count of the rows in the group is greater than two. In other words, more than two clients of a consultant must have a credit limit of less than $10,000 for the consultant to appear in the results. If you use both the WHERE clause and the GROUP BY clause in the same query, the WHERE clause should always come first. 108 Chapter 3 Only clients with credit limits of less than $10,000 will be included Only consultants with more than two clients will be included HAVING clause WHERE clause FIGURE 3-47 SQL query that includes WHERE and HAVING clauses The query results appear in Figure 3-48.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Oracle Performance Tuning for 10gR2

  • Gavin JT Powell(Author)
  • 2011(Publication Date)
  • Digital Press

    (Publisher)

  6.1 The SELECT Statement 141 Chapter 6 EXPLAIN PLAN SET statement_id='TEST' FOR SELECT customer_id, SUM(order_id) FROM orders GROUP BY customer_id HAVING customer_id < 10 ; Query Cost Rows Bytes Sort ------------------------------ ------ ------- --------- ---------SELECT STATEMENT on 366 10 70 FILTER on SORT GROUP BY on 366 10 70 TABLE ACCESS FULL on ORDERS 112 172304 1206128 6.1.4.3.1 The MODEL Clause The MODEL clause extends the GROUP BY clause, allowing display of data into multiple dimensions, including calculations between rows. It is very similar to a spreadsheet program such as Excel. The MODEL clause provides additional OLAP-type functionality and is more applicable to data warehousing as opposed to Internet OLTP databases. However, using the MODEL clause can possibly reduce the number of tables in mutable joins and remove the need for set operators such as UNION, INTERSECT, and MINUS. Set operators can help merge multiple queries together. The HAVING clause filter can help performance because it filters, allowing the return and processing of fewer rows. The HAVING clause fil-tering shown in the query plans shows that HAVING clause filtering is always executed after the GROUP BY sorting process . 6.1.4.4 ROLLUP, CUBE, and GROUPING SETS The ROLLUP, CUBE, and GROUPING SETS clauses can be used to cre-ate breaks and subtotals for groups. The GROUPING SETS clause can be used to restrict the results of ROLLUP and CUBE clauses. Before the advent of the ROLLUP and CUBE clauses, producing the same types of results would involve extremely complex SQL statements. These queries involved extensive use of temporary tables, or perhaps even of PL/SQL. ROLLUP, CUBE, and GROUPING SETS clauses are more applicable to reporting and data warehouse functionality, but they can have significant benefits to performance. The following examples simply show the use of the ROLLUP, CUBE, and GROUPING SETS clauses:

  Sign up to read

  Learn more about book