find how many there are??

[snowman2344]

I have a field in mysql named PAYMENT as follows

 

PAYMENT

25

15

10

10

10

25

25

25

15

 

I need to count how many of each payment types there are and add them up

 

EG output would be

 

3 people paid $10 for a total of $30

2 people paid $15 for a total of $30

4 people paid $25 for a total of $100

 

The total revenue for the show is $160

 

Thanks

[smerny]

this is something i came up with quick, not tested

 

$amount = 0;
$number = 0;
$revenue = 0;

$search = "SELECT PAYMENT FROM table ORDER BY PAYMENT ASC";
$result = mysql_query($search) or die ("SQL Error:" . mysql_error());
while ($row = mysql_fetch_array($result))
{
  if ($amount == $row['PAYMENT'])
  {
    $number += 1;
  }
  else
  {
    if($number > 0)
    {
      $total = $number * $amount;
      $revenue += $total;
      echo $number ." people paid $". $amount ." for a total of $". $total ."<br />";
      $number = 1;
      $amount = $row['PAYMENT'];
    }
  }
}
echo "<br />The total revenue for the show is $". $revenue;

[akitchin]

that's a very intensive way of doing it when you can use MySQL's built-in functions. try something like this:

 

SELECT COUNT(*) AS total, payment AS payment_amount FROM table GROUP BY payment ORDER BY payment

 

the total amount of money paid for each amount is simply the total multiplied by the payment amount. you could even put it into the query:

 

SELECT COUNT(*) AS total_times, SUM(payment) AS total_paid, payment AS payment_amount FROM table GROUP BY payment ORDER BY payment