Search code examples
sqlsql-serveraveragedimensional-modeling

SQL: Time Dimension and Calculating Averages

Although not exactly the same, I have a SQL Server 2008-R2 database schema that pretty much looks like this...enter image description here

I'm struggling to get my head round how I would query the DB to get the average number of product sales of a over a given time frame?

Eg, What's the average number of sales per month of for each product between 2012 and 2014.

My main problem is working out how to include zero figures in the average to take months where a product wasn't sold at all into consideration.

Any hints appreciated.

Solution

  • If I understand well your needs, you want to know the average number of product sold between 2012 and 2014 included, which is the number of product sold divided by 36.

    In order to manage products which have not been sold during this period, the key is to do a right join (or left) on the dim_product table : you'll then have at least one line per product (in this case, the units_sold field will be null). The request should be something like this (untested!)

    SELECT product_name, units_sold FROM dim_product RIGHT JOIN fact_sales ON dim_product.Id=Fact_sales.product_id WHERE date...;

    Then, you can adapt it to make the sum on units_sold (if there are not any, you'll have zero), and divide by the number of months.

    It should look like this (Even more untested than the previous one) :

    SELECT product_name, SUM(units_sold)/36 as average_per_month FROM dim_product RIGHT JOIN fact_sales ON dim_product.Id=Fact_sales.product_id WHERE date...;

    And the documentation link : https://technet.microsoft.com/en-us/library/ms187518%28v=sql.105%29.aspx