Description

The SQL standard provides two additional aggregate operators. These use the polymorphic value “ALL” to denote the set of all values that an attribute can take. The two operators are:

• with data cube that it provides all possible combinations than the argument attributes of the clause.
• with roll up that it provides the aggregates obtained by considering the attributes in order from left to right compared how they are listed in the argument of the clause.

SQL standard versions that support these features: 1999,2003,2006,2008,2011.

Examples

Consider this table:

Food | Brand | Total_amount | —–– | —— | ———— | Pasta | Brand1 | 100 | Pasta | Brand2 | 250 | Pizza | Brand2 | 300 |

With cube

select Food,Brand,Total_amount
from Table
group by Food,Brand,Total_amount with cube

Food | Brand | Total_amount | —–– | —— | ———— | Pasta | Brand1 | 100 | Pasta | Brand2 | 250 | Pasta | ALL | 350 | Pizza | Brand2 | 300 | Pizza | ALL | 300 | ALL | Brand1 | 100 | ALL | Brand2 | 550 | ALL | ALL | 650 |

With roll up

select Food,Brand,Total_amount
from Table
group by Food,Brand,Total_amount with roll up

Food | Brand | Total_amount | —–– | —— | ———— | Pasta | Brand1 | 100 | Pasta | Brand2 | 250 | Pizza | Brand2 | 300 | Pasta | ALL | 350 | Pizza | ALL | 300 | ALL | ALL | 650 |