Database Management SQL in 10 Minutes Book Ch. 9 & 10

The COUNT() Function

COUNT() does just that: It counts. Using COUNT(), you can determine the number of rows in a table or the number of rows that match a specific criterion. COUNT() can be used two ways: • Use COUNT(*) to count the number of rows in a table, whether columns contain values or NULL values. • Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULL values. This first example returns the total number of customers in the Customers table: SELECT COUNT(*) AS num_cust FROM Customers;

Using Aggregate Functions

It is often necessary to summarize data without actually retrieving it all, and SQL provides special functions for this purpose.

The MAX() function

MAX() returns the highest value in a specified column. MAX() requires that the column name be specified, as seen here:

The MIN() function

MIN() does the exact opposite of MAX();, it returns the lowest value in a specified column. Like MAX(), MIN() requires that the column name be specified, as seen here: SELECT MIN(prod_price) AS min_price FROM Products;

SELECT clause ordering

Refresher on how to order a query search: SELECT - Columns or expressions to be returned FROM - Table to retrieve data from WHERE - Row-level filtering GROUP BY - Group specification HAVING - Group-level filtering ORDER BY - Output sort order

The SUM() function

SUM() is used to return the sum (total) of the values in a specific column. SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;

Combining Where and Having clauses

So is there ever a need to use both WHERE and HAVING clauses in one statement? Actually, yes, there is. Suppose you want to further filter the above statement so that it returns any customers who placed two or more orders in the past 12 months. To do that, you can add a WHERE clause that filters out just the orders placed in the past 12 months. You then add a HAVING clause to filter just the groups with two or more rows in them. SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

Group By function

Groups are created using the GROUP BY clause in your SELECT statement. SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id; *Pg lesson 10*

The AVG() Function

AVG() is used to return the average value of a specific column by counting both the number of rows in the table and the sum of their values. AVG() can be used to return the average value of all columns or of specific columns or rows. SELECT AVG(prod_price) AS avg_price FROM Products;

Combining aggregate functions

All the examples of aggregate function used thus far have involved a single function. But actually, SELECT statements may contain as few or as many aggregate functions as needed. Look at this example: SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM Products;

Aggregates on Distinct() values

To only include unique values, specify the DISTINCT argument. The following example uses the AVG() function to return the average product price offered by a specific vendor. It is the same SELECT statement used above, but here the DISTINCT argument is used so that the average only takes into account unique prices: SELECT AVG(DISTINCT prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';

Having function

you might want a list of all customers who have made at least two orders. To obtain this data you must filter based on the complete group, not on individual rows. HAVING is very similar to WHERE. In fact, all types of WHERE clauses you learned about thus far can also be used with HAVING. The only difference is that WHERE filters rows and HAVING filters groups. SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;

Group By conditions:

• GROUP BY clauses can contain as many columns as you want. This enables you to nest groups, providing you with more granular control over how data is grouped. • If you have nested groups in your GROUP BY clause, data is summarized at the last specified group. In other words, all the columns specified are evaluated together when grouping is established (so you won't get data back for each individual column level). • Every column listed in GROUP BY must be a retrieved column or a valid expression (but not an aggregate function). If an expression is used in the SELECT, that same expression must be specified in GROUP BY. Aliases cannot be used. • Most SQL implementations do not allow GROUP BY columns with variable length datatypes (such as text or memo fields). • Aside from the aggregate calculations statements, every column in your SELECT statement must be present in the GROUP BY clause. • If the grouping column contains a row with a NULL value, NULL will be returned as a group. If there are multiple rows with NULL values, they'll all be grouped together. • The GROUP BY clause must come after any WHERE clause and before any ORDER BY clause.