Calculating group specific averages

Difficulty

Objective

You would like to calculate the median price for each product sold by a store during a specified time period. You know how to do this in SQL by using the AVG and GROUP BY commands, and you would like to produce the same results using 1010data.

SQL solution

In SQL, you can execute multiple operations in a single line of code. The following line calculates the average weekly sales for each SKU sold in each store.

SELECT sku,store,week(date), AVG(xsales) 
FROM sales_item_detail 
GROUP BY sku,store,WEEK(date);

Although it is relatively simple to complete this calculation, SQL has certain drawbacks. For example, you can't easily save query results and you can't build on you queries without nesting SELECT statements.

1010data Macro Language solution

<base table="pub.doc.retail.altseg.sales_detail_transid"/>
<link table2="pub.doc.retail.calendar" col="trans_date" col2="trans_date"/>
<tabu label="Average Weekly Sales" breaks="sku,store,fw,fy">
  <break col="sku" sort="up"/>
  <tcol source="xsales" fun="avg" name="avg_sales" 
  label="Average`Weekly`Sales"/>
</tabu>

For this example, the Date column from each table is used to link the sales detail table and the fiscal calendar, so you can correctly add the fiscal week to each transaction.

The Average Weekly Sales tabulation uses the columns Sku, Store, Fiscal Week, and Fiscal Year as break columns. This is similar to the columns given to GROUP BY in SQL. It is important to include both the fiscal week and fiscal year because if a table contains multiple years, just grouping by fiscal week can improperly group values. The <tcol> operation creates a column containing the average extended sales for each specified group.

For the break column sku, an additional operation, <break> is used to sort the column in order to mimic the results in SQL and illustrate that they are indeed the same.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

<tabu>

<link>