Aggregate Queries

Aggregate queries summarize (aggregate) individual facts. The measure values are typically summed, even though count is also a common aggregate. Two examples are discussed in this section.

Daily Sales Aggregation

The dimensional query in Listing 4.2 gives you the daily sales summary. The aggregation of the order amounts and number of orders is done by date. Note that the join between the sales_prder_fact table and date_dim table is on their surrogate keys.

Listing 4.2: Daily Aggregation

 /*****************************************************************/ /*                                                               */ /* daily_aggregation.sql                                         */ /*                                                               */ /*****************************************************************/ SELECT   date , SUM (order_amount) , COUNT(*) FROM   sales_order_fact a , date_dim b WHERE a.order_date_sk = b.date_sk GROUP BY date ORDER BY date ; /* end of script                                                 */

Run the query using this command.

 mysql> \. c:\mysql\scripts\daily_aggregation.sql

Here is the output of this query.

 +----------------+--------------------+-----------+ | date           | SUM (order_amount) |  COUNT(*) | +----------------+--------------------+-----------+ | 2007-02-05     |           58000.00 |        10 | | 2007-02-06     |          195000.00 |         6 | +----------------+--------------------+-----------+ 2 rows in set (0.03 sec)

The query result shows the daily total order amounts (sum) and the number of orders (count) of all orders.

Annual Aggregation

The dimensional query in Listing 4.3 gives you the annual sales summary. The order amounts and the number of orders are not only aggregated by date, but also by product and customer city. The three joins, between the fact table and each of the three dimension tables (date, product, and customer dimensions), are on the surrogate keys.

Listing 4.3: Annual aggregation

 /*****************************************************************/ /*                                                               */ /* annual_aggregation.sql                                        */ /*                                                               */ /*****************************************************************/ SELECT year, product_name, customer_city, SUM (order_amount),   COUNT(*) FROM   sales_order_fact a , date_dim b , product_dim c , customer_dim d WHERE     a.order_date_sk = b.date_sk AND a.product_sk = c.product_sk AND a.customer_sk = d.customer_sk GROUP BY year, product_name, customer_city ORDER BY year, product_name, customer_city ; /* end of script                                                 */

Run the script as follows:

 mysql> \. c:\mysql\scripts\annual_aggregation.sql

Here is the output of the query

 +------+-----------------+---------------+---------------+---------+ | year | product_name    | customer_city | SUM           |COUNT(*) | |      |                 |               |(order_amount) |         | +------+-----------------+---------------+---------------+---------+ | 2007 | Floppy Drive    | Mechanicsburg |      70000.00 |       5 | | 2007 | Floppy Drive    | Pittsburgh    |       8000.00 |       1 | | 2007 | Hard Disk Drive | Mechanicsburg |      46000.00 |       2 | | 2007 | Hard Disk Drive | Pittsburgh    |      34000.00 |       3 | | 2007 | LCD Panel       | Mechanicsburg |      61000.00 |       3 | | 2007 | LCD Panel       | Pittsburgh    |      34000.00 |       2 | +------+-----------------+---------------+---------------+---------+ 6 rows in set (0.03 sec)

The query result presents the annual total order amounts (sum) and number of orders (count) of all orders grouped by years, products, and cities.