Daily Average Sales for items in mySQL

by David Hamilton   Last Updated July 12, 2019 17:06 PM

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.

Tags : mysql

Related Questions

The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM

getting innodb buffer usage

Updated August 24, 2018 15:06 PM