Aggregating Data - DBA 1: Introduction to Database Administration

Aggregate functio ns create summaries fro m yo ur ro ws and co lumns o f data. The simplest aggregate functio n is COUNT, which is a ro w co unt. Let's try it o ut by retrieving the number o f ro ws (pro ducts) ro ws in the Pro ducts table.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT COUNT(*) FROM Products;

+---+ | COUNT(*) | +---+ | 6 | +---+

1 row in set (0.00 sec) mysql>

Note

If yo u've changed the Pro ducts table fro m the last lesso n, yo u'll see a different number.

Here, the COUNT aggregate has given us the number o f ro ws in the Pro ducts table. It wo uld be nice to be able to answer mo re co mplex questio ns, such as "Ho w many pro ducts are in each catego ry?" To answer that questio n, we must GROUP BY the Catego ry co lumn o n the Pro ducts table.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT Category, COUNT(*) as NumberOfProducts

-> FROM Products -> GROUP BY Category; +---+---+ | Category | NumberOfProducts | +---+---+ | Database Theory | 3 | | MySQL | 1 | | SQL | 2 | +---+---+ 3 rows in set (0.00 sec)

mysql>

There are three bo o ks in the "Database Theo ry" catego ry, o ne in "MySQL," and two in "SQL."

What if yo u want to find the average price and the maximum price o f the pro ducts in each catego ry? Yo u can use multiple aggregates in the same query.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT Category, AVG(Price) as AveragePrice, MAX(Price) as MaximumPrice -> FROM Products

-> GROUP BY Category;

+---+---+---+ | Category | AveragePrice | MaximumPrice | +---+---+---+ | Database Theory | 38.296667 | 44.99 | | MySQL | 44.990000 | 44.99 | | SQL | 22.490000 | 29.99 | +---+---+---+ 3 rows in set (0.00 sec)

mysql>

OBSERVE:

mysql> SELECT Category, AVG(Price) as AveragePrice, MAX(Price) as MaximumPrice -> FROM Products

-> GROUP BY Category;

In this query, we specified the co lumns o n which we wo uld like to apply the aggregate. The AVG aggregate is o ver the Price co lumn, and so is the MAX aggregate.

MySQL allo ws yo u to o mit the GROUP BY clause in so me queries, but if yo u o mit GROUP BY, yo ur query may no t return the same results. Try it:

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT Category, AVG(Price) as AveragePrice, MAX(Price) as MaximumPrice

-> FROM Products;

+---+---+---+ | Category | AveragePrice | MaximumPrice | +---+---+---+ | Database Theory | 34.143333 | 44.99 | +---+---+---+ 1 row in set (0.00 sec)

mysql>

Yo u o nly see o ne result ro w.

If yo u want, yo u can disable this MySQL-specific behavio r by changing the SQL_MODE.

Type the fo llo wing at the MySQL pro mpt:

mysql> set SQL_MODE='ONLY_FULL_GROUP_BY';

Query OK, 0 rows affected (0.00 sec) mysql>

No w when yo u run a query witho ut a GROUP BY yo u will see an erro r (assuming yo ur sql_mo de is still set to 'ONLY_FULL_GROUP_BY').

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT Category, AVG(Price) as AveragePrice, MAX(Price) as MaximumPrice

-> FROM Products;

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP col umns is illegal if there is no GROUP BY clause

Fo r mo re info rmatio n abo ut MySQL's use o f GROUP BY, see MySQL's web site.

There are many mo re useful aggregate functio ns, such as SUM and MIN. See MySQL's web site fo r additio nal aggregate functio ns. Be sure to take so me time and experiment with these!

Functions

While aggregates allo w yo u to summarize many ro ws o f info rmatio n, functio ns allo w yo u to create brand-new co lumns o f data. The sto re keeps track o f pro ducts, and the number o f pro ducts in invento ry. Suppo se yo ur manager wants to kno w the do llar value o f the pro ducts in sto ck.

In the last lesso n we used a jo in to sho w the pro ducts alo ng with the quantity in sto ck. Let's write that query again.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT P.ProductCode, P.Price, I.QuantityInStock as Qty

-> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

+---+---+---+ | ProductCode | Price | Qty | +---+---+---+ | artofsql | 44.99 | 52 | | databaseid | 29.95 | 0 | | mysqlspp | 44.99 | 5 | | relationaldb | 14.99 | NULL | | sqlhks | 29.99 | 32 | | sqltuning | 39.95 | 105 | +---+---+---+ 6 rows in set (0.00 sec) mysql>

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT P.ProductCode, P.Price, I.QuantityInStock as Qty, P.Price * I.QuantityInS tock as DollarValue

-> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

+---+---+---+---+ | ProductCode | Price | Qty | DollarValue | +---+---+---+---+ | artofsql | 44.99 | 52 | 2339.48 | | databaseid | 29.95 | 0 | 0.00 | | mysqlspp | 44.99 | 5 | 224.95 | | relationaldb | 14.99 | NULL | NULL | | sqlhks | 29.99 | 32 | 959.68 | | sqltuning | 39.95 | 105 | 4194.75 | +---+---+---+---+ 6 rows in set (0.00 sec)

mysql>

OBSERVE:

P.Price * I.QuantityInStock as DollarValue

The result includes has a new Do llarValue co lumn which is the pro duct o f Price and Quant it y.

It might seem strange to have a NULL Do llarValue. But it is the desired result, since Qty is NULL fo r that ro w. So ho w can we find the to tal do llar value fo r o ur pro ducts? Let's co mbine o ur functio n with the SUM aggregate!

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT P.ProductCode, P.Price, I.QuantityInStock as Qty, SUM(P.Price * I.Quantit yInStock) as DollarValue

-> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP col umns is illegal if there is no GROUP BY clause

mysql>

We are asking fo r a summary o f all ro ws in the Pro ducts and Invento ry tables, alo ng with the Pro ductCo de, Price, and QuantityInSto ck. This do esn't make sense—what is the Pro ductCo de fo r the total sum? The database canno t give yo u a co rrect answer, so yo u get the no nsensical result.

To co rrect this pro blem, yo u have two o ptio ns: yo u can remo ve the extra co lumns fro m yo ur query, o r yo u can GROUP BY tho se co lumns. Since we are lo o king fo r the to tal o f all ro ws, let's remo ve the co lumns.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT SUM(P.Price * I.QuantityInStock) as TotalDollarValue

-> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

+---+ | TotalDollarValue | +---+ | 7718.86 | +---+ 1 row in set (0.00 sec) mysql>

This query is much better, and gives us the desired result.

MySQL has many functio ns that o perate o n strings, numbers, and dates. Yo u can find mo re info rmatio n abo ut these functio ns at the MySQL web site.

Conditionals

No w that yo u kno w abo ut aggregates and functio ns, yo u're able to answer so me interesting questio ns! Suppo se yo ur bo ss has a new invento ry pro blem fo r yo u to so lve. If there are less than 10 pro ducts in the sto re, she wants the sto re manager to reo rder. If there are between 10 and 50 pro ducts, the invento ry level is fine. If there are 50 o r mo re

pro ducts, the manager needs to send the extra sto ck to the wareho use.

Ho w do we query the database to find the details abo ut the invento ry? One way is to use a CASE statement.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT P.ProductCode, P.Title, I.QuantityInStock as Qty,

-> CASE WHEN I.QuantityInStock < 10 THEN 'Reorder'

-> WHEN I.QuantityInStock >= 10 AND I.QuantityInStock < 50 THEN 'In Stock'

-> WHEN I.QuantityInStock >= 50 THEN 'Extra to Warehouse'

-> END as Action

-> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

OBSERVE:

mysql> SELECT P.ProductCode, P.Title, I.QuantityInStock as Qty, -> CASE WHEN I.QuantityInStock < 10 THEN 'Reorder'

-> WHEN I.QuantityInStock >= 10 AND I.QuantityInStock < 50 THEN 'In Stock' -> WHEN I.QuantityInStock >= 50 THEN 'Extra to warehouse'

-> END as Action -> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

CASE statements have two parts—co m pariso ns and re sult s. Co m pariso ns are checked in o rder. As so o n as a TRUE co mpariso n is fo und, the co rrespo nding re sult is returned.

This is almo st the result we are lo o king fo r; ho wever, that NULL fo r "The Relatio nal Database Dictio nary" is tro ubling. Since we have never had invento ry fo r that bo o k, we sho uld set the actio n to be "Place Initial Order." But remember, we're dealing with NULLs so we have to be careful.

Type the fo llo wing at the MySQL pro mpt:

mysql> SELECT P.ProductCode, P.Title, IFNULL(I.QuantityInStock, 0) as Qty,

-> CASE WHEN I.QuantityInStock IS NULL THEN 'Place Initial Order'

-> WHEN I.QuantityInStock < 10 THEN 'Reorder'

-> WHEN I.QuantityInStock >= 10 AND I.QuantityInStock < 50 THEN 'In Stock'

-> WHEN I.QuantityInStock >= 50 THEN 'Extra to warehouse'

-> END as Action

-> FROM Products as P

-> LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode);

OBSERVE:

mysql> SELECT P.ProductCode, P.Title, IFNULL(I.QuantityInStock, 0) as Qty,

-> CASE WHEN I.QuantityInStock IS NULL THEN 'Place Initial Order'

We used the IFNULL co nditio nal to check the QuantityInSto ck co lumn. NULLs in that co lumn are replaced by zero s (0 ). We also added a co mpariso n to the CASE statement—I.Quant it yInSt o ck IS NULL—to display the actio n "Place Initial Order."

In this lesso n, yo u learned ho w to co mbine data fro m yo ur co lumns in meaningful ways to create new co lumns o f data and summaries o f ro ws. In the next lesso n, yo u'll learn o ne way to sto re yo ur new queries in the database fo r future use. See yo u then!

This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. See http://creativecommons.org/licenses/by-sa/3.0/legalcode for more information.

In document DBA 1: Introduction to Database Administration (Page 51-57)