Calculated Fields

It is a simple matter to perform calculations in a query. The result of the expression is placed in a new column in the result set. You can give the calculated field a name, too. These calculated fields are sometines called computed columns. Don't confuse them with aggregate functions which do calculations in a sense but don't create a new column in the result set.

Example
Here is a simple example from the bookstore database. (Remember that the use bookstore statement only applies to SQL Server and MySQL. Oracle and Access don't use it.)

With Cube

Background
SQL does not normally put total lines in the same result set as the detail like an accounting spreadsheet. By adding with cube to the group by clause you will get the total lines as shown below. It is similar to with rollup but subtotals each dimension. For our examples we will use a tiny fact table from the pets database. You can download the database script here. It is a tiny fact table that summarizes animals available by store. We originally obtained the table from the internet, I forget where. At some point we may add dimension tables to get a true star schema.

Notes
The result set returned by a group by with cube is a valid SQL result set. It can be filtered or manipulated like any other result set. The total line or lines returned are not the total of the groups shown. They are the totals in the table. An example below will make this clearer. A NULL is placed in the dimension column where a summary row has been inserted.

Syntax
SELECT statement...
...
GROUP BY groupbylist
WITH CUBE

Example 1
Show the animals available by store with subtotals for each store type and a grand total. In addition show the subtotals for each animal type.

Example 2
Same above but retain only rows with number available greater than 10. Note that the grand total row still shows 63 animals available.

With Rollup

Background
SQL does not normally put a total line in the same result set as the detail like an accounting spreadsheet. By adding with rollup to the group by clause you will get the total line as shown below. For our examples we will use a tiny fact table from the pets database. You can download the database script here. It is a tiny fact table that summarizes animals available by store. We originally obtained the table from the internet, I forget where. At some point we may add dimension tables to get a true star schema.

Notes
The result set returned by a group by with rollup is a valid SQL result set. It can be filtered or manipulated like any other result set. The total line or lines returned are not the total of the groups shown. They are the totals in the table. An example below will make this clearer. A NULL is placed in the column where the summary row has been inserted.

Syntax
SELECT statement...
...
GROUP BY groupbylist
WITH ROLLUP

Example 1
Show the animals available by store with subtotal for each animal type and a grand total.

Example 2
Same above but retain only rows with number available greater than 10. Note that the grand total row still shows 63 animals available.