CODEWITHSUNDEEP
mysql
Xaver
Xaver

Reputation: 11652

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 weekis always greater or equal to it's predecessors.

I would like to get the count of last available weeklike 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

Upvotes: 0

Views: 48

Answers (2)

Strawberry
Strawberry

Reputation: 33935

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;

Upvotes: 0

Zelldon
Zelldon

Reputation: 5516

Add an where clause to your query so that you can define the week

SELECT current.name, current.week, current.count - last.count
FROM table as current 
JOIN table as last
ON current.name = last.name
AND current.week = last.week - 1
WHERE current.week = 11

If you want the result for all weeks I would use a subselect:

SELECT current.name, current.week, current.count,
   current.count - (SELECT last.count FROM table as last WHERE last.name = current.name AND last.week = current.week-1) as diff
FROM table as current

Upvotes: 1

Related Questions