Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 10-04-2009
Member
 
Join Date: May 2008
Posts: 351
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,1DESCclients.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).
Reply With Quote
  #2  
Old 10-04-2009
Member
 
Join Date: Feb 2008
Posts: 194
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
Reply With Quote
  #3  
Old 11-04-2009
Member
 
Join Date: May 2008
Posts: 351
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.idc.field1c.field2count(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.idc.field1c.field2count(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
Reply With Quote
  #4  
Old 11-04-2009
Member
 
Join Date: Feb 2008
Posts: 194
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
Reply With Quote
  #5  
Old 11-04-2009
Member
 
Join Date: May 2008
Posts: 351
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.idc.field1c.field2f.invoicepayeecount(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 
Reply With Quote
  #6  
Old 11-04-2009
Member
 
Join Date: Feb 2008
Posts: 194
Re: List clients by amount of bills

At that time, trying to get into the join condition

PHP Code:
SELECT c.idc.field1c.field2f.invoicepayeecount(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 
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "List clients by amount of bills"
Thread Thread Starter Forum Replies Last Post
wlan clients unable to ping lan clients with WAG320N connoisseur Networking & Security 3 25-06-2012 06:46 PM
Vodafone cheats me on my bills Sera-phina India BroadBand 5 12-05-2011 12:57 PM
Airtel Bills For Limited Plan Count avesh India BroadBand 4 19-01-2011 11:14 PM
How to Pay Airtel Broadband Bills online Tarank India BroadBand 3 10-10-2010 12:46 AM
How to view BSNL bills online? Paulino India BroadBand 3 15-03-2010 02:15 PM


All times are GMT +5.5. The time now is 06:49 AM.