 |
 |
|
Download: Solaris 8 Migration Assistant
Rapidly move your Solaris 8 application environments to new systems running Solaris 10 with the Solaris 8 Migration Assistant. Reduce migration risk while taking advantage of increased performance, reliability and security of the latest SPARC hardware platforms and Solaris 10 OS. Click here
»
|
 |
 |
|
GROUP BY clause
The GROUP BY clause will gather all of the rows together that contain data in the
specified column(s) and will allow aggregate functions to be performed on the one or
more columns. This can best be explained by an example:
GROUP BY clause syntax:
SELECT column1,
SUM(column2)
FROM "list-of-tables"
GROUP BY "column-list";
Let's say you would like to retrieve a list of the highest paid salaries in each dept:
SELECT max(salary), dept
FROM employee
GROUP BY dept;
This statement will select the maximum salary for
the people in each unique department. Basically, the salary for the person who makes the
most in each department will be displayed. Their,
salary and their department will be returned.
Multiple Grouping Columns - What if I wanted to display their lastname too?
For example, take a look at the items_ordered table. Let's say you want
to group everything of quantity 1 together, everything of quantity 2 together,
everything of quantity 3 together, etc. If you would like to determine
what the largest cost item is for each grouped quantity (all quantity 1's, all quantity 2's, all quantity 3's, etc.), you would enter:
SELECT quantity, max(price)
FROM items_ordered
GROUP BY quantity;
Enter the statement in above, and take a look at the results to see
if it returned what you were expecting. Verify that the maximum price
in each Quantity Group is really the maximum price.
Review Exercises
- How many people are in each unique state in the customers table?
Select the state and display the number of people in each. Hint:
count is used to count rows in a column, sum works on numeric data only.
- From the items_ordered table, select the item, maximum price, and
minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups.
- How many orders did each customer make? Use the items_ordered table.
Select the customerid, number of orders they made, and the sum of their orders.
Click the Group By answers link below if you have any problems.
Answers to these Exercises
Enter SQL Statement here:
| 
