SELECT  --invoicelinetable.*, 
        inventorytable.INVENTORYCODE, 
        LCASE(inventorytable.DESCRIPTION) AS Expr1, 
        inventorytable.UNIT, 
        IF(COUNT(inventorytable.INVENTORYCODE) > 1,"T","fff") AS test, 
        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,
                    invoicetable.DATEINVOICE
        ORDER BY salesordertable.SUBCLIENTCODE, inventorytable.INVENTORYCODE;