Explain the GROUP BY and HAVING commands and give one example of each using Leeb

Question

Explanation / Answer

SQL - Group By SQL GROUP BY aggregates (consolidates and calculates) column values into a single record value. GROUP BY requires a list of table columns on which to run the calculations. At first, this behavior will resemble the SELECT DISTINCT command we toyed with earlier. Advertise on Tizag.com SQL Group By: USE mydatabase;SELECT customerFROM ordersGROUP BY customer; SQL Results: customer A+Maintenance Gerald Garner Tizag Here, SQL has consolidated like values and returned those that are unique. In this case, we have actually duplicated the behavior of SELECT DISTINCT , but you have also seen firsthand how GROUP BY accepts a table column as a list and consolidates like customer values. To unleash the true power of GROUP BY , it isnecessary to include at least one mathematical (aggregate) function, and to do so we will utilizethe SUM() function to calculate how many total items have been purchased by each of our customers. SQL Code: USE mydatabase;SELECT customer, SUM(quantity) AS "Total Items"FROM ordersGROUP BY customer; SQL Results: customer Total Items A+Maintenance 14 Gerald Garner 5 Tizag 23 With the addition of the aggregate SUM() function, we've let SQL calculate how many products have been ordered by each customer and returned them for viewing with a single query statement. Taking a look at another example, we can also figure out how many of each product was ordered with the use of a single query statement. SQL Code: USE mydatabase;SELECT product, SUM(quantity) AS "Total Items"FROM ordersGROUP BY product; SQL Results: product Total Items 19" LCD Screen 10 Hanging Files 25 HP Printer 4 Stapler 3 GROUP BY would also be a great way to calculate how much total cash of our customers has spent. Let's take a look at what that query may look like SQL: HAVING Clause The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns. The syntax for the HAVING clause is: SELECT column1, column2, ... column_n, aggregate_function (expression) FROM tables WHERE predicates GROUP BY column1, column2, ... column_n HAVING condition1 ... condition_n; aggregate_function can be a function such as SUM , COUNT , MIN , or MAX . Example using the SUM function: For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned. SELECT department, SUM(sales) as "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000; Example using the COUNT function: For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned. SELECT department, COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.