finding the number of 1's in my table in order

Question

I have a table of customers with a 1 recorded against their customerid on different dates. I would like to find the sum of the 1's recorded in descending order. I'm using MySQL and php Thanks

Answer 1

As i get you right, this is simple sql request what u need:

SELECT COUNT(id) as total from customers

Just make in php:

   $sql="SELECT COUNT(id) from customers";
    $query=mysql_query($sql) or die(mysql_error());
      $res=mysql_fetch_assoc($query);

 $summ=$res['total'];   //<- Your summ (i.e. quantity of rows in table)

Btw, you can use mysql_num_rows instead.

Or explain please more accurately what output you need. To make sorting by date or any other dependency you will need to make other request using WHERE clause and date comparison.

Answer 2

My guess is that you want the sum of records marked with 1 per customer and sort that result in descending order? If so, the following should do the trick :

select cust.id, sum(cone.one) as number_ones 
from customers as cust
inner join customer_ones as cone on cone.id=cust.id
group by cust.id
order by number_ones desc

This is assuming that 'one' is the column containing ones (and only contains 0 or 1 - otherwise you will have to add WHERE cone.one = 1), customers is your customer table and customer_ones is the table containing your customer data.