|
SQL for the Rest of Us
( Part 3 of 5 )
Grouping Results and Aggregate Functions
Rodney POWELL
Microsoft MVP - Excel |
|
This is the continuation of a five-part introduction to
Structured Query Language (SQL). For your convenience, here are
links back to the earlier parts: 1 and
2.
There are aggregate functions that can be used in a SQL
statement with a GROUP BY clause to create summaries of
groups in a recordset. When an aggregate function is combined with
the GROUP BY clause, one record is returned for each unique value in
the field designated in the GROUP BY clause.
| Function |
Returns |
| AVG |
Average value of a specified field |
| COUNT |
Number of records in a query |
| SUM |
Sum of the values in a specified field |
| MAX |
Largest value in a specified field |
| MIN |
Smallest value in a specified field |
The following is an example of a statement using GROUP BY and an
aggregate function.
SELECT CustID, Count(*)
FROM Orders
WHERE TtlOrderAmt > 1000
GROUP BY CustID;
This could return a result set showing each customer and a count of
how many of the customers' orders were greater than $1,000.
There is also a HAVING clause that is similar to the WHERE
clause, except that it evaluates the condition(s) after the grouping
has occurred.
SELECT CustID, Count(*)
FROM Orders
WHERE ShipState IN('FL','TX','VA')
GROUP BY CustID
HAVING TtlOrderAmt > 1000;
This could return a result set showing a count by customer of orders
> $1,000 shipped to Florida, Texas, or Virginia.
Note that although the the HAVING clause and the WHERE clause are
similar, they aren't interchangeable. HAVING is only used in
conjunction with the GROUP BY clause.
Something to add?
Let me know.
|
|
|
|
![Microsoft Office : Excel - Visual Basic for Applications [ VBA ] Software](img/1ptrans.gif){kind=small}
