revaxarts - 2 months ago 6
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

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;
``````