Based on order history, I'm trying to calculate the daily average sales for a given product form a vendor.
I have a table containing the line-item detail for the sales order. The relevant columns are:
item_id, vendor_id, ship_qty, post_date
I want to get a daily total of product sales for a given item. Item id's can be duplicated across vendors, but not within the same vendor id. With that total I'm trying to derive the average quantity sold per day.
I started by creating a view that provides the sum by item on a given day:
daily_sales_by_item as SELECT SUM(ship_qty) AS item_total, item_id,vendor_id, post_date FROM order_history GROUP BY vendor_id, item_id, post_date
Then I'm selecting an average from this view and grouping by the vendor_id and item_id as a sub-query:
SELECT item_id,vendor_id from daily_sales_by_item d where d.item_id = o.item_id and d.vendor_id = o.vendor_id
I feel like I must be way of using this approach, because the numbers I'm getting are totally inaccurate.
Any help is greatly appricated.