I encounter this problem when trying to work out a dimensional database design. Basicially I haave a fact table (sale_fact) which contains a surrogate key, several foreign keys (pid for product id, cid for country id and date), and sales data. The granularity or this fact table is on daily basis. How do I sum up the sales data for weeks (or months), depending on different combinations of those foreign keys? For example, I want to sum up sales data of product 1 in country 1 in this week (pid = 1 and cid = 1 and date = Monday), sales data of product 2 in country 2 in the same week (pid = 2 and cid = 2 and date = Monday). How do I achieve this data in one SQL? The sample result set would be like:
| id | pid | cid | date | total |
| 1 | 1 | 1 | 2013-11-04 | 100 |
| 2 | 1 | 2 | 2013-11-04 | 90 |
| 3 | 1 | 3 | 2013-11-04 | 80 |
| 4 | 2 | 1 | 2013-11-04 | 91 |
......more data
Why don't you use something like select sum(sales) as 'total' from table where date = Monday groupby pid, cid...