Group By

The GROUP BY clause combines the results for identical values in a column or expression. This clause is typically used in conjunction with aggregate functions to generate a single figure for each unique value in a column or expression. For example, to obtain the number of orders for each part number in the orders table:

select partno, count(*) from orders
group by partno;

The preceding query returns one row for each part number in the orders table, even though there can be many orders for the same part number.

Nulls are used to represent unknown data, and two nulls are typically not considered to be equal in SQL comparisons. However, the group by clause treats nulls as equal and returns a single row for nulls in a grouped column or expression.

Grouping can be performed on multiple columns or expressions. For example, to display the number of orders for each part placed each day:

select odate, partno, count(*) from orders
group by odate, partno;

If you specify the group by clause, all columns in the select clause must be aggregate functions, columns specified in the group by clause or expressions, all of whose column references also appear in the columns or expressions of the group by clause.

Note: Aggregate functions cannot appear anywhere in a group by clause. Derived columns may appear in a group by clause, but must be referenced by their ordinal number in the column list.

Having

The HAVING clause filters the results of the group by clause, in the same way the where clause filters the results of the select...from clauses. The having clause uses the same restriction operators as the where clause.

For example, to return orders for each part for each day in the past week:

select odate, partno, count(*) from orders
group by odate, partno
having odate >= (date('today') - '1 week');

Any columns or expressions contained in the having clause must follow the same limitations previously described for the Select clause.

Order By

The ORDER BY clause allows you to specify the columns on which the results table is to be sorted. For example, if the employees table contains the following data:

then this query:

select emanager, ename, edept from employees
order by emanager, edept, ename

produces the following list of managers, the departments they manage, and the employees in each department:

and this query:

select ename, edept, emanager from employees
order by ename

produces this alphabetized employee list:

To display result columns sorted in descending order (reverse numeric or alphabetic order), specify order by column_name desc. For example, to display the employees in each department from oldest to youngest:

select edept, ename, eage from employees
order by edept, eage desc;

If a nullable column is specified in the order by clause, nulls are sorted to the end of the results table.

Note: If the order by clause is omitted, the order of the rows in the results table is not guaranteed to have any relationship to the storage structure or key structure of the source tables.

In union selects, the result column names must either be the column names from the select clause of the first select statement, or the number of the result column. For example:

select dcolumn from dtest
union
select zcolumn from ztest
order by dcolumn

In addition to specifying individual column names as the ordering-expressions of the order by clause, the results table can also be sorted on the value of some expression.

For example, the query:

select ename, edept, emanager from employees
       order by emanager+edpt

produces the employee list ordered on the concatenation of the emanager and edept values.

The only requirement when specifying column names or expressions in the order by clause is that all referenced columns must exist in one of the tables contained in the from clause.

Union

The UNION clause combines the results of select statements into a single result table. For example, to list all employees in the table of active employees plus those in the table of retired employees:

select ename from active_emps
union
select ename from retired_emps;

By default, the union clause eliminates any duplicate rows in the result table. To retain duplicates, specify union all. Any number of select statements can be combined using the union clause, and both union and union all can be used when combining multiple tables.

Unions are subject to the following restrictions:

• The select statements must return the same number of columns.
• The columns returned by the select statements must correspond in order and data type, although the column names do not have to be identical.
• The select statements cannot include individual order by clauses.

To sort the result table, specify the order by clause following the last select statement. The result columns returned by a union are named according to the first select statement.

By default, unions are evaluated from left to right. To specify a different order of evaluation, use parentheses.

Any number of select statements can be combined using the union clause. There is a maximum of 126 tables allowed in any query.