revaxarts revaxarts - 3 months ago 8
MySQL Question

Compare count values from the current with the last week

I'm not sure if my title is correct but I think it's a problem with the order.

I count

total
values for specific
name
's per
week
:

ID | name | week | total
========================
1 | Foo | 9 | 2
2 | Bar | 9 | 4
3 | Lou | 9 | 3
4 | Zoo | 9 | 5
...
21 | Foo | 10 | 10
22 | Bar | 10 | 12
23 | Lou | 10 | 14
24 | Zoo | 10 | 16
...
45 | Foo | 11 | 16
46 | Bar | 11 | 12
48 | Lou | 11 | 24
49 | Zoo | 11 | 24


The
total
of a
week
is always greater or equal to it's predecessors.

I would like to get the count of last available
week
like this:

name | week | count
===================
Foo | 11 | 6 //= 16-10
Bar | 11 | 0 //= 12-12
Lou | 11 | 10 //= 24-14
Zoo | 11 | 8 //= 24-18


That's what I currently have but it returns always the first available week from the table

SELECT a.*,
a.total - b.total AS count
FROM table AS a
LEFT JOIN table AS b
ON a.name = b.name
AND a.week = b.week + 1
GROUP BY b.name


Also I would like to get a list with the values from all weeks and their
count
's

Answer

E.g.:

SELECT x.name
     , x.week
     , x.total-MAX(y.total) count
  FROM my_table x
  JOIN my_table y
    ON y.name = x.name
   AND y.week < x.week
  JOIN (SELECT MAX(week) week FROM my_table) z
    ON z.week = x.week
 GROUP 
    BY x.name
     , x.week
 ORDER 
    BY x.id;