Get last record in each group and SUM some of them

i have a problem with sql query to mysql to take the last record in each group and sum some field in one query.i have a table:

name        date    interested  made_call
andrew.h    2011-02-04      10        10
andrew.h    2011-02-11      20        10
andrew.h    2011-02-13      2         10
sasha.g  2011-02-11      5         20
sasha.g  2011-02-12      5         1

i need to sum made_call column grouping by name and return the last record from interested. here what i want to get in result:

name        date    interested  made_call
andrew.h    2011-02-13      2         30
sasha.g  2011-02-12      5         21

i tried to get result with this query

SELECT a.name,a.date,a.interested,sum(made_call) as made_call
FROM `resultboard` a 
WHERE a.attendence = 1开发者_C百科 
AND NOT EXISTS (select 1 from resultboard where name = a.name
       and id > a.id and attendence = 1)
GROUP BY name

but in result i got

  andrew.h  2011-02-13      2         10
  sasha.g    2011-02-12      5         1

so the query didnot sum, just return the last record from group help)

That may be a little slow if the table is very big, but it will get the wanted result:

SELECT a.name, t.date, a.interested, t.calls
FROM resultboard a 
JOIN (SELECT name, MAX(date) AS date, SUM(made_call) AS calls FROM resultboard 
     GROUP BY name) AS t
     ON a.name = t.name AND a.date = t.date

Your WHERE clause is eliminating all but the last row from consideration as part of the sum.

In some other DB's you could use the LAST aggregate function. MySQL doesn't have that, but you can emulate it like so for your case:

SELECT
    a.name,
    SUBSTRING_INDEX(
      GROUP_CONCAT(CAST(a.date AS CHAR) ORDER BY date desc),
      ',', 1
    ) AS date,
    SUBSTRING_INDEX(
      GROUP_CONCAT(CAST(a.interested AS CHAR) ORDER BY date desc),
      ',', 1
    ) AS interested,
    sum(made_call) as made_call
FROM `resultboard` a 
WHERE a.attendence = 1 
GROUP BY name

It might not be fast on large data sets, but it should at least do the job if my research is correct. I haven't tested this, so YMMV.

I think that using WITH ROLLUP for GROUP BY modifier may help you. http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

Edit; I got i wrong , no need for WITH ROLLUP

SELECT r.name,  
       MAX(r.date) as date,  
       (SELECT r2.interested FROM resultboard r2 WHERE r2.name = r.name ORDER BY r.date DESC LIMIT 1),  
       SUM(made_call) as made_call  
FROM resultboard r 
GROUP BY name;