Clever Geek Handbook

MYSQL: sum of last record

I need help running a query to add selected records.

An example of entries in the "tblA" table :

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 make a query of 1 query to sum the points for John, with the condition that if the level is the same for more than two years, then only the points from the last year will be counted.

For example: in the above case, only the following records should contain the added points. (entries for 2012 should be ignored because there is a later year (2013) that has a level 1. So 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.

+3


source to share


4 answers


To get all names and their dots (following the logic), you can 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 are only interested in the "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

+5


source


Maybe try also:



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
+1


source


Maybe this might work for you:

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


source


The answer to @lpg's question is great if the level just goes up. However, if a drop in level is possible, the solution will be different. Here's a general 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

0


source






More articles:


All Articles