SELECT
        inventorytable.INVENTORYCODE,
        LCASE(inventorytable.DESCRIPTION) AS Expr1,
        inventorytable.UNIT,
        IF(COUNT(inventorytable.INVENTORYCODE) > 1, "1", inventorycolortable.SHORTCUT) AS ter,
        UCASE(IF(ISNULL(salesordertable.SUBCLIENTCODE),"CASH & CARRY", salesordertable.SUBCLIENTCODE)) AS SUBCLIENTCODE,
        invoicetable.DATEINVOICE
FROM    ((inventorycolortable RIGHT JOIN (invoicelinetable LEFT JOIN inventorytable ON invoicelinetable.INVENTORYID = inventorytable.INVENTORYID) ON inventorycolortable.COLORID = inventorytable.COLORID) LEFT JOIN salesordertable ON invoicelinetable.SALESID = salesordertable.SALESID) LEFT JOIN invoicetable ON invoicelinetable.INVOICEID = invoicetable.INVOICEID WHERE invoicetable.invoiceid = 10
        -- *** NOTE USING GROUP BY, YOU SHOULD INCLUDE THE COLUMNS TO GROUP
        GROUP BY    inventorytable.INVENTORYCODE,
                    inventorytable.DESCRIPTION,
                    inventorytable.UNIT,
                    inventorytable.INVENTORYCODE,
                    salesordertable.SUBCLIENTCODE
        ORDER BY salesordertable.SUBCLIENTCODE, inventorytable.INVENTORYCODE;