CODEWITHSUNDEEP
mysqlsql
Meng Hai
Meng Hai

Reputation: 127

MYSQL: sum of latest record

I need help with query statement to add selected records.

Example of records in table "tblA":

Name, Year, Level, Points, Item
John, 2012, 1,     2,      bag
John, 2012, 1,     1,      book
John, 2013, 1,     1,      pen
John, 2013, 1,     1,      pencil
John, 2014, 2,     3,      hat
John, 2014, 2,     1,      ruler
Kent, 2014, 2,     2,      bag
Mic,  2014, 2,     2,      bag
Dan,  2014, 2,     2,      bag
Tim,  2014, 2,     2,      bag

Is it possible to do a 1 statement query to sum the points for John with condition that if the Level is the same for more than 2 years, then only the points for the latest year will be considered.

Eg: in the above case, only the following records should have the points added. (the 2012 records should be ignored because there is a later year (2013) that has Level 1. Thus John should have 6 points.

John, 2013, 1, 1, pen
John, 2013, 1, 1, pencil
John, 2014, 2, 3, hat
John, 2014, 2, 1, ruler

Thanks in advance.

Upvotes: 3

Views: 410

Answers (4)

JimmyB
JimmyB

Reputation: 12610

Maybe also try it like this:

select tblA.name, tblA.year, sum(tblA.points)
from tblA 
inner join (
    select name, level, max(year) as yr
    from tblA
    group by name, level ) as yearTbl
on tblA.name=yearTbl.name and tblA.year=yearTbl.yr and tblA.level=yearTbl.level
group by tblA.name, tblA.year

Upvotes: 1

Bulat
Bulat

Reputation: 6979

Answer of @lpg is great if level only goes up. However, if it is possible for level to drop solution will be different. Here is a generic solution:

SELECT Levels.Name, SUM(points) AS totalPoints FROM Levels
INNER JOIN (
 SELECT Name, Year FROM (
  SELECT Name,  MAX(Year) Year FROM
  (
    SELECT 
     l.*,
     @prev_level as previous_level,
     CASE WHEN @prev_level IS NULL THEN FALSE 
       ELSE  @prev_level != l.level 
     END AS level_change,
     @prev_level := l.level as set_new_level
    FROM Levels l, (SELECT @prev_level:= NULL) c ) t
    GROUP BY Name, level_change, level) tt
 GROUP BY Name, Year) ttt 
    ON Levels.Name = ttt.Name AND Levels.year = ttt.year
GROUP BY Levels.Name;

http://sqlfiddle.com/#!2/57643/21

Upvotes: 0

Sagar Joon
Sagar Joon

Reputation: 1417

May be this can work for you :

select name ,sum(points) from tblA group by name , year ,level having count(*) 
= 2 ;

Upvotes: 0

lpg
lpg

Reputation: 4937

To get all names and their points (following your logic), you could try:

SELECT tblA.Name, SUM(points) AS totalPoints FROM tblA
JOIN (
SELECT MAX(year) year, Name, Level
FROM tblA
GROUP BY Name, Level) tblA2
ON tblA.Name = tblA2.Name
AND tblA.year = tblA2.year
AND tblA.Level = tblA2.Level
GROUP BY Name

If you're only interested on 'John' points, then:

SELECT tblA.Name, SUM(points) AS totalPoints FROM tblA
JOIN (
SELECT MAX(year) year, Name, Level
FROM tblA
WHERE Name = 'John'
GROUP BY Name, Level) tblA2
ON tblA.Name = tblA2.Name
AND tblA.year = tblA2.year
AND tblA.Level = tblA2.Level
GROUP BY Name

SQL Fiddle demo: http://sqlfiddle.com/#!2/75f478/9

Upvotes: 5

Related Questions