[Resolved] COUNT column --- than SUM?!?

sgtpepper

I am trying to count all the values in a column....than add them together to get a total.

The below code.....gives me the numbers I need....but I do not know how to take these numbers....and add them together to get a total. I tried using SUM but no luck. Any ideas?

SELECT sender_name,COUNT(sender_name) as bubba from recipients GROUP BY sender_name

LordShryku

Can you use rollup?
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Modifiers.html

sgtpepper

No....

"SELECT sender_name,COUNT(sender_name) as bubba from recipients GROUP BY sender_name WITH ROLLUP

Gives me the error:
You have an error in your SQL syntax near 'WITH ROLLUP' at line 1

LordShryku

Well, I'd suspect you're only solution from there would be to use php to do the sum. :bemused:

sgtpepper

yes - what would be the best way of doing this?

LordShryku

Just a loop I guess

$sql = "SELECT sender_name,
               COUNT(sender_name) as bubba 
          from recipients 
      GROUP BY sender_name";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   $gtotal = 0; # Establish the variable
   while($row = mysql_fetch_array($res)) {
      $gtotal += $row['bubba']; # Add the counts
   }
}

devinemke

why not this:

SELECT COUNT(field you want to count), SUM(field you want to sum) from table

LordShryku

Well, I suspect because the sum is on the count, and the group by statement would split that into sender name sums instead of grand total.

sgtpepper

Yes that is correct.