SELECT
        pr.DESCRIPTION,
        CONCAT(pr.REV_CATEGORY,IF(pr.REVENUE_SUBCATEGORY='','',CONCAT(':',pr.REVENUE_SUBCATEGORY))) AS Account,
        sum(ii.QUANTITY) AS Qty,
        cast(
                sum(ii.PRICE) AS DECIMAL (10, 2)
        ) TotalSales
FROM
        invoices i
LEFT OUTER JOIN payments p ON i.payment_id = p.payment_id
JOIN customers c ON i.chargeto_customer_id = c.customer_id
JOIN invoice_items ii ON i.invoice_id = ii.invoice_id
JOIN products pr ON ii.product_id = pr.product_id
WHERE
        i.invtype = 'POS'
AND i.postdate >= '{STARTDATE}'
AND i.postdate <= '{ENDDATE}'
AND ii.IS_TAX_ITEM = 0
AND (
        i.payment_id <= 0
        OR (
                (
                        unix_timestamp(p.postdate) - unix_timestamp(i.postdate)
                ) > 9
        )
)
AND voidedinvoice = 0
GROUP BY
        pr.product_id
HAVING
        qty > 0
AND totalsales >= 0
ORDER BY
        totalsales DESC