Can this Mysql query be amended to display invoice only once for the same idinvoice

Question

The query below

SELECT
<output name="c.name" title="Name" />,
<output name="c.reference" title="Reference No." />,
<output name="i.idinvoice" title="Invoice ID" />,
<output replace="(SUM(v.quantity * s.charges))/COUNT(i.idinvoice)" />As Invoice
<output replace="p.amount" />AS Amount,
<output replace="p.idpayment" title="Payment ID" />,
<output replace="p.cheque_no title="Cheque No" />,
<output replace="p.pdatetime" title="Processed Time" />,
<output replace="p.user" title="Processed By" type="user" attribute="username" />
FROM inv i
LEFT JOIN invitem v ON v.idinvoice = i.idinvoice
LEFT JOIN service s ON s.idservice = v.idservice
LEFT JOIN payment p ON p.idinvoice = i.idinvoice
LEFT JOIN address a ON a.idaddress = i.idaddress
LEFT JOIN client c ON c.idclient = a.idclient
WHERE a.idaddress = '<input name="idaddress" />'
GROUP BY i.idinvoice, p.amount, p.idpayment, p.cheque_no, p.pdatetime, p.user
ORDER BY i.idinvoice DESC

Answer 1

If you only want the idinvoice shown once, your GROUP BY should only include the IDINVOICE. You'll have to decide how to display the other parameters (SUM/AVG/random/none...).

SELECT
<output name="c.name" title="Name" />,
<output name="c.reference" title="Reference No." />,
<output replace="i.idinvoice" title="Invoice ID" />,
<output replace="(SUM(v.quantity * s.charges))/COUNT(i.idinvoice)" />AS Invoice,
<output replace="(p.amount)" data="float" />As Amount,
<output name="p.idpayment" title="Payment ID" />,
<output replace="p.cheque_no" title="Cheque No" />,
<output replace="p.pdatetime" title="Processed Time" />,
<output replace="p.user" title="Processed By" type="user" attribute="username" />
FROM inv i
LEFT JOIN invitem v ON v.idinvoice = i.idinvoice
LEFT JOIN service s ON s.idservice = v.idservice
LEFT JOIN payment p ON i.idinvoice = p.idinvoice
LEFT JOIN address a ON a.idaddress = i.idaddress
LEFT JOIN client c ON c.idclient = a.idclient
WHERE a.idaddress = '<input name="idaddress" />'
GROUP BY i.idinvoice
ORDER BY i.idinvoice DESC