SELECT *
FROM (
        SELECT
        products.DESCRIPTION AS Description,
        vendors.VENDOR_Name AS Vendor,
        products.SIZE_DESC AS Size,
        products.COLOR_DESC AS Color,
        CONCAT(products.REV_CATEGORY,':',products.REVENUE_SUBCATEGORY) AS Account,
        SUM(trans.QUANTITY) AS Inventory,
	sold.SOLD AS Sold,
        ROUND(sold.SOLD_PER_WEEK,1) AS Sold_Per_Week,
        ROUND(SUM(trans.QUANTITY)/sold.SOLD_PER_WEEK,1) AS Weeks_On_Hand
        FROM (
                (
                        SELECT
                        invoices.POSTDATE,
                        invoice_items.PRODUCT_ID,
                        invoice_items.QUANTITY*(-1) AS QUANTITY,
                        'SALE' AS Trans_Type
                        FROM
                        invoice_items
                        Inner Join products ON products.PRODUCT_ID = invoice_items.PRODUCT_ID
                        Inner Join invoices ON invoices.INVOICE_ID = invoice_items.INVOICE_ID
                        WHERE
                        invoices.VOIDEDINVOICE =  0 AND
                        (products.PRODUCT_TYPE =  'ICHILD' OR
                        products.PRODUCT_TYPE =  'ISINGLE') AND
                        products.INACTIVE = 0
                )
                UNION ALL
                (
                        SELECT
                        restock.POSTDATE,
                        restock.PRODUCT_ID,
                        restock.QUANTITY,
                        restock.RTYPE AS Trans_Type
                        FROM
                        restock
                        Inner Join products ON products.PRODUCT_ID = restock.PRODUCT_ID
                        WHERE
                        (products.PRODUCT_TYPE =  'ICHILD' OR
                        products.PRODUCT_TYPE =  'ISINGLE') AND
                        products.INACTIVE = 0
                )
                ORDER BY
                PRODUCT_ID ASC,
                POSTDATE ASC
        ) trans
        Left Join (
                SELECT
                invoice_items.PRODUCT_ID,
                sum(invoice_items.QUANTITY) AS SOLD,
                sum(invoice_items.QUANTITY)/((DATEDIFF('{ENDDATE}','{STARTDATE}')+1)/7) AS SOLD_PER_WEEK
                FROM
                invoice_items
                Inner Join products ON products.PRODUCT_ID = invoice_items.PRODUCT_ID
                Inner Join invoices ON invoices.INVOICE_ID = invoice_items.INVOICE_ID
                WHERE
                DATE(invoices.POSTDATE) BETWEEN '{STARTDATE}' AND '{ENDDATE}' AND
                invoices.VOIDEDINVOICE =  0 AND
                (products.PRODUCT_TYPE =  'ICHILD' OR
                products.PRODUCT_TYPE =  'ISINGLE') AND
                products.INACTIVE = 0
                GROUP BY
                invoice_items.PRODUCT_ID
        ) sold ON sold.PRODUCT_ID = trans.PRODUCT_ID
        Inner Join products ON products.PRODUCT_ID = trans.PRODUCT_ID
        Inner Join vendors ON vendors.VENDOR_ID = products.VENDOR_ID
        GROUP BY
        trans.PRODUCT_ID
) output
ORDER BY
Account ASC,
Description ASC,
Size ASC,
Color ASC
/*allow-multigym*/