List clients by amount of bills

[Elsie]

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:

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).

[Ashok.M]

select myfields, count(distinct invoiceid) as nbinvoices
from client left join invoices on client.id=invoices.clientid
group by myfields
order by nbinvoices desc

[Elsie]

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 :

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 :

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

[Ashok.M]

Added as not too much (for I am against the checks and paypal) you added in your circumstances

[Elsie]

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 :

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 

[Ashok.M]

At that time, trying to get into the join condition

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