Specific Queries

A specific query selects and aggregates the facts on a specific dimension value. The two examples show the application of dimensional queries in specific queries.

Monthly Storage Product Sales

The monthly_storage.sql script in Listing 4.4 aggregates sales amounts and the number of orders every month.

Listing 4.4: Specific query (monthly storage product sales)

 /*****************************************************************/ /*                                                               */ /* monthly_storage.sql                                           */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   product_name , month_name , year , SUM (order_amount) , COUNT(*) FROM   sales_prder_fact a , product_dim b , date_dim c WHERE     a.product_sk = b.product_sk AND a.order_date_sk = c.date_sk GROUP BY   product_name , product_category , month_name , year HAVING product_category = 'Storage' ORDER BY   year , month name ; /* end of script                                                 */

Run the script using this command.

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

Here is the output of the query:

 Database changed +-----------------+------------+------+------------------+---------+ | product_name    | month_name | year | SUM(order_amount)| COUNT(*)| +-----------------+------------+------+------------------+---------+ | Hard Disk Drive | February   | 2007 |         65000.00 |       2 | | Floppy Drive    | February   | 2007 |         55000.00 |       2 | | Hard Disk Drive | February   | 2007 |         15000.00 |       3 | | Floppy Drive    | February   | 2007 |         23000.00 |       4 | +-----------------+------------+------+------------------+---------+ 4 rows in set (0.00 sec)

The query result shows the monthly total order amounts (sum) and the number of orders (count), grouped by the individual storage products.

Quarterly Sales in Mechanisburg

The query in Listing 4.5 is another specific query. It produces the quarterly aggregation of the order amounts in Mechanicsburg.

Listing 4.5: Specific query (quarterly sales in Mechanicsburg)

 /*****************************************************************/ /*                                                               */ /* quarterly_mechanicsburg.sql                                   */ /*                                                               */ /*****************************************************************/ USE dw; SELECT   customer_city , quarter , year , SUM (order_amount) , COUNT (order_sk) FROM   sales_order_fact a , customer_dim b , date_dim c WHERE     a.customer_sk = b.customer_sk AND a.order_date_sk = c.date_sk GROUP BY   customer_city , quarter , year HAVING customer_city = 'Mechanicsburg' ORDER BY   year , quarter; /* end of script                                                 */

Run the script using this command.

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

Here is the query result.

 Database changed +---------------+---------+------+-----------------+---------------+ | customer_city | quarter | year |SUM(order_amount)|COUNT(order_sk)| +---------------+---------+------+-----------------+---------------+ | Mechanicsburg |       4 | 2007 |       177000.00 |            10 | +---------------+---------+------+-----------------+---------------+ 1 row in set (0.00 sec)

The query result shows the quarterly total order amounts (sum) and the number of orders (count) for Mechanicsburg.