Hi I am using mySql and i need a little help with my code, its nearly right but just one little thing going wrong. I have 3 columns in my results. 'entries_per_week' which gets the new entries each week so there could be 5 one week 3 the next 8 the next and so on. 'total_entries' which keeps track of the total entries each week so it keeps adding up the 'entries_per_week' column. Then the '6WK_total' which adds up the previous 6 weeks total. So basically adds up 6 of the 'previous_total_entries'. I nearly have it working but at the start of a new year the 6WK_total starts new from that year instead of adding up the entries from the previous year. Heres my code.
SELECT a.*
, sum(b.total_entries) 6WK_total
FROM
( SELECT x.*
, SUM(y.entries_per_week) total_entries
FROM
( SELECT YEARWEEK(date_created) week_created
, COUNT(*) entries_per_week
FROM entries
GROUP
BY week_created
) x
JOIN
( SELECT YEARWEEK(date_created) week_created
, COUNT(*) entries_per_week
FROM entries
GROUP
BY week_created
) y
ON y.week_created <= x.week_created
GROUP
BY x.week_created
) a
JOIN
( SELECT x.*
, SUM(y.entries_per_week) total_entries
FROM
( SELECT YEARWEEK(date_created) week_created
, COUNT(*) entries_per_week
FROM entries
GROUP
BY week_created
) x
JOIN
( SELECT YEARWEEK(date_created) week_created
, COUNT(*) entries_per_week
FROM entries
GROUP
BY week_created
) y
ON y.week_created <= x.week_created
GROUP
BY x.week_created
) b
ON b.week_created BETWEEN a.week_created - (6 - 1) AND a.week_created
GROUP
BY week_created;
And what happens, here are some results, notice the bold result, my desired results are below in the other table. It should be 1056 like the desired results table below not 248
+--------------+-------+-----------------------+-----------+
| week_created | total | total_entries | 6WK_total |
+--------------+-------+-----------------------+-----------+
| 201149 | 49| 131 | 243 |
| 201150 | 37| 168 | 411 |
| 201151 | 37| 205 | 614 |
| 201152 | 18| 223 | 837 |
| 201201 | 25| 248 | 248 |
| 201202 | 33| 281 | 529 |
| 201203 | 66| 347 | 876 |
| 201204 | 70| 417 | 1293|
| 201205 | 61| 478 | 1771|
| 201206 | 88| 566 | 2337|
| 201207 | 72| 638 | 2727|
| 201208 | 72| 710 | 3156|
| 201209 | 67| 777 | 4030|
+--------------+-------+-----------------------+-----------+
Desired results, notice at week_create '201206' the 6 week total would be the 6 results previous in the total_entries so it would be 566 + 478 + 417 + 347 + 281 + 248 = 2337.
+--------------+-------+-----------------------+-----------+
| week_created | total | total_entries | 6WK_total |
+--------------+-------+-----------------------+-----------+
| 201149 | 49| 131 | 243 |
| 201150 | 37| 168 | 411 |
| 201151 | 37| 205 | 614 |
| 201152 | 18| 223 | 837 |
| 201201 | 25| 248 | 1056|
| 201202 | 33| 281 | 1256|
| 201203 | 66| 347 | 1472|
| 201204 | 70| 417 | 1127|
| 201205 | 61| 478 | 1989|
| 201206 | 88| 566 | 2337|
| 201207 | 72| 638 | 2727|
| 201208 | 72| 710 | 3156|
| 201209 | 67| 777 | 4030|
+--------------+-------+-----------------------+-----------+
Tom
Results keep starting from the start at the start
Posted - 06/08/2012 : 10:47:44
