SQL Keep running count, after inner Join

user_13191061 0
Viewed 54

I am working with a local mysql server and I have two tables.

Table A:

ID | Name 
1    Joe
2    Bob
3    John

Table B:

ID |    DATE    |  Name_ID | Point
1    2010-01-01       1       1
2    2011-01-01       1       1
3    2013-01-01       1      -1
4    2010-01-01       2      -1
5    2012-01-01       2      -1
6    2013-01-01       2      -1
7    2014-01-01       2       1

For each ID in Table A, I am trying to get the

  1. max date and
  2. a running count of their points. (dates and points being in Table B).

Wanted Result:

Name | Latest_Date | Point_total
John      NULL         NULL
Joe     2013-01-01      1
Bob     2014-01-01     -2

So far I Have this Query:

SELECT DISTINCT a.Name, b.Date, SUM(b.point) OVER (PARTITION BY b.Name_ID) Point_total FROM 
TableA a 
LEFT OUTER JOIN TableB b 
ON a.ID = b.Name_ID 
INNER JOIN  (
SELECT b.Name_ID, MAX(b.Date) Latest_Date 
FROM TableB b GROUP BY b.Name_ID ) m 
ON (m.Latest_Date = b.Date or (b.Date is NULL)) AND 
(b.Name_ID = m.Name_ID OR (b.Name_ID is NULL)) 
ORDER BY Name_ID;

The resulting table does output the correct max date, but the running total only takes into account the point of the entry with the max date.

What am I missing here?

join group-by sum mysql sql
3 Answers

This just looks like aggregation:

SELECT a.Name, MAX(b.Date), SUM(b.point) as Point_total
FROM TableA a LEFT OUTER JOIN
     TableB b 
     ON a.ID = b.Name_ID 
GROUP BY a.name
user_11440350

That's an aggregation query:

select a.name, max(b.date) latest_date, sum(b.point) point_total
from a
left join b on b.name_id = a.id
group by a.id, a.name
user_106767160 
SELECT A.Name, MAX(B.Date) as Max_Date, SUM(b.point) as Total_Points
FROM TableA as A
    LEFT JOIN TableB as B 
    ON A.ID = B.Name_ID 
GROUP BY A.name
user_77184090
Related