List clients by amount of bills
I have a CLIENTS table, and I want to list/order clients by amount of bills they have.
Invoices are in the INVOICES table and are assigned to clients via a simple field "clientid" that I would have also been called "belongstotheclientwhoseIDis".
here's what I tried:
PHP Code:
SELECT * FROM clients ORDER BY (SELECT invoices.id FROM invoices WHERE invoices.clientid = clients.id AND invoicespayee != 1 LIMIT 0,1) DESC, clients.id DESC
what it does is that it displays all the clients who have bills first, followed by customers who have no bill at all. But what I would like to see is those who most bills first (customers who have 3 invoices must appear before those who have only 2, for example).
Re: List clients by amount of bills
select myfields, count(distinct invoiceid) as nbinvoices
from client left join invoices on client.id=invoices.clientid
group by myfields
order by nbinvoices desc
Re: List clients by amount of bills
by walking against super small detail, I need to sort by number of unpaid bills and not by total number of bills, so I was in my initial request "invoicepayee! = 1"
here's what I now :
PHP Code:
SELECT c.id, c.field1, c.field2, count(DISTINCT f.id) AS nbinvoices
FROM clients c LEFT JOIN invoices f ON c.id=f.clientid
GROUP BY c.id
ORDER BY nbinvoices DESC $pagination
what would be nice is being able to do something like that :
PHP Code:
SELECT c.id, c.field1, c.field2, count(DISTINCT f.invoicepayee != 1) AS nbinvoices
FROM clients c LEFT JOIN invoices f ON c.id=f.clientid
GROUP BY c.id
ORDER BY nbinvoices DESC $pagination
but it does not, of course. An idea on how to proceed ?
MERCI INFINIMENT VOUS ÊTES MON AMOUR DE TOUS LES JOURS
Re: List clients by amount of bills
Added as not too much (for I am against the checks and paypal) you added in your circumstances
Re: List clients by amount of bills
Thank you very much! it works well
However, this class of clients by number of unpaid bills they have, but all clients who have no outstanding bills are not at all. I could of course make a second request would eliminate those who have unpaid bills, to see those who have no outstanding bills as a result, but is this the right way to proceed (using 2 queries)?
here's what I now :
PHP Code:
SELECT c.id, c.field1, c.field2, f.invoicepayee, count(DISTINCT f.id) AS nbinvoices
FROM clients c LEFT JOIN invoices f ON c.id=f.clientid
WHERE f.invoicepayee != '1'
GROUP BY c.id
ORDER BY nbinvoices DESC $pagination
Re: List clients by amount of bills
At that time, trying to get into the join condition
PHP Code:
SELECT c.id, c.field1, c.field2, f.invoicepayee, count(DISTINCT f.id) AS nbinvoices
FROM clients c LEFT JOIN invoices f ON c.id=f.clientid AND f.invoicepayee != '1'
GROUP BY c.id
ORDER BY nbinvoices DESC $pagination
