In my previous post i talked about group functions which are operating on groups of rows (as group of rows can be considered the whole table). In this post I am going to concentrate on the possibility of creating groups and using them in the SELECT statements.
To create group of rows we need first some common attribute. For example:
Considering orders, we can create groups using as attribute order_date, customer_id ect.
After we have chosen the common attribute we should put it into the select statement. It is done using the GROUP BY Clause. The GROUP BY Clause appears in the SELECT Statement after the WHERE clause.
Examples:
It is important that all columns listed in the SELECT clause that are not in a function must be included in the GROUP BY clause, otherwise we will get an ORA-00979: not a GROUP BY expression
Example:
The proper statement will be:
Now, let see how we can produce a report about the total amount of orders made by each customer:
No comments:
Post a Comment