Overall Sum issue

Barb54

Hi,
I have the grouping and totals on the groups based on "nick" but now I need an "final total" for nick on the point column only, not to include the count this time. I need it to show up as the last lind for each nick.

Here is what I have so far:

SELECT nick, gameid, point_type, point_id, Sum( pointamt ) , Sum( pointcount )
FROM records AS Totals
GROUP BY nick, gameid, point_id
ORDER BY nick

I get my nicks in alpha order, the totals in each point_type and point_id for the amt and count. Works nicely BUT I also need a Final Total of point only for nick. Can someone help me please?

Thanks in advance,
Barb

LordShryku

Thought about using ROLLUP?
http://www.mysql.com/doc/en/GROUP-BY-Modifiers.html

Barb54

Thank you so much. I didn't even know that code existed, lol. But then I am new at php/mysql so it figures. I am on my way to try it out now to see how it works.

Wish me luck. I well report back and mark this resolved if I have no problems with it.

Thanks ,
Barb :p

LordShryku

It's a fairly common function in most SQL DB's. Some even utilize cube, which provides even more choices in your grouping modifiers. Sadly, MySQL is way behind the times...

Barb54

I tried your solution and this is error I got:

Error
SQL-query :

SELECT nick, gameid, point_type, point_id, Sum( pointamt ) , Sum( pointcount )
FROM records AS Totals
GROUP BY nick
WITH ROLLUP, gameid, point_id
ORDER BY nick

MySQL said:
#1235 - This version of MySQL doesn't yet support 'ROLLUP'

I have to work with this version because it's the one my Hosting service uses and I am locked in for another 8 mths, lol. Any other suggestions?

Thanks again for your help in advance.........
Barb

LordShryku

Well then, you're stuck with using a php solution....

Something like this should leave you with an array($ttls)
of totals in the form of "nick" => "total"

$sql = "SELECT nick, 
               gameid, 
               point_type, 
               point_id, 
               sum(pointamt) as points,
               sum(pointcount) as pointcount
          FROM records AS Totals
      GROUP BY nick, gameid, point_id
      ORDER BY nick";
$res = mysql_query($sql);
if(mysql_num_rows($res) > 0) {
   $hold = "";
   $pnts = 0;
   $ttls = array();
   while($row=mysql_fetch_array($res)) {
      if($hold != $row['nick']) {
         $ttls[$hold] = $pnts;
         $hold = $row['nick'];
      }
      else {
         $pnts += $row['points'];
      }
   }
}