Question: Write a query, which will output the user count today, as well as from 7 (uc7), 14 (uc14), 30 (uc30) days ago
Table: num_users
+------------+------------+
| dateid | user_count |
+------------+------------+
| 2014-12-31 | 1010 |
| 2014-12-30 | 1000 |
| 2014-12-29 | 990 |
| 2014-12-28 | 980 |
| 2014-12-27 | 970 |
| 2014-12-26 | 960 |
| 2014-12-25 | 950 |
| 2014-12-24 | 940 |
| 2014-12-23 | 930 |
| 2014-12-22 | 920 |
| 2014-12-21 | 910 |
| 2014-12-20 | 900 |
| 2014-12-19 | 890 |
| 2014-12-18 | 880 |
| 2014-12-17 | 870 |
| 2014-12-16 | 860 |
| 2014-12-15 | 850 |
| 2014-12-14 | 840 |
| 2014-12-13 | 830 |
| 2014-12-12 | 820 |
| 2014-12-11 | 810 |
| 2014-12-10 | 800 |
| 2014-12-09 | 790 |
| 2014-12-08 | 780 |
| 2014-12-07 | 770 |
| 2014-12-06 | 760 |
| 2014-12-05 | 750 |
| 2014-12-04 | 740 |
| 2014-12-03 | 730 |
| 2014-12-02 | 720 |
| 2014-12-01 | 710 |
+------------+------------+
Desired Output:
+------------+------+------+------+------+
| dateid | uc | uc7 | uc14 | uc30 |
+------------+------+------+------+------+
| 2014-12-31 | 1010 | 940 | 870 | 710 |
| 2014-12-30 | 1000 | 930 | 860 | 0 |
| 2014-12-29 | 990 | 920 | 850 | 0 |
| 2014-12-28 | 980 | 910 | 840 | 0 |
| 2014-12-27 | 970 | 900 | 830 | 0 |
| 2014-12-26 | 960 | 890 | 820 | 0 |
| 2014-12-25 | 950 | 880 | 810 | 0 |
| 2014-12-24 | 940 | 870 | 800 | 0 |
| 2014-12-23 | 930 | 860 | 790 | 0 |
| 2014-12-22 | 920 | 850 | 780 | 0 |
| 2014-12-21 | 910 | 840 | 770 | 0 |
| 2014-12-20 | 900 | 830 | 760 | 0 |
| 2014-12-19 | 890 | 820 | 750 | 0 |
| 2014-12-18 | 880 | 810 | 740 | 0 |
| 2014-12-17 | 870 | 800 | 730 | 0 |
| 2014-12-16 | 860 | 790 | 720 | 0 |
| 2014-12-15 | 850 | 780 | 710 | 0 |
| 2014-12-14 | 840 | 770 | 0 | 0 |
| 2014-12-13 | 830 | 760 | 0 | 0 |
| 2014-12-12 | 820 | 750 | 0 | 0 |
| 2014-12-11 | 810 | 740 | 0 | 0 |
| 2014-12-10 | 800 | 730 | 0 | 0 |
| 2014-12-09 | 790 | 720 | 0 | 0 |
| 2014-12-08 | 780 | 710 | 0 | 0 |
| 2014-12-07 | 770 | 0 | 0 | 0 |
| 2014-12-06 | 760 | 0 | 0 | 0 |
| 2014-12-05 | 750 | 0 | 0 | 0 |
| 2014-12-04 | 740 | 0 | 0 | 0 |
| 2014-12-03 | 730 | 0 | 0 | 0 |
| 2014-12-02 | 720 | 0 | 0 | 0 |
| 2014-12-01 | 710 | 0 | 0 | 0 |
+------------+------+------+------+------+
How do I properly do this? I tried my solution as below but it does not result in the right solution
SELECT dateid AS today,
(SELECT SUM(user_count) FROM num_users WHERE dateid = dateid) AS uc,
(SELECT SUM(user_count) FROM num_users WHERE dateid - 7) AS uc7,
(SELECT SUM(user_count) FROM num_users WHERE dateid - 14) AS uc14,
(SELECT SUM(user_count) FROM num_users WHERE dateid - 14) AS uc30
FROM num_users
This produces the presented output:
SELECT num_users.dateid, num_users.user_count AS uc,
(SELECT user_count FROM num_users AS A WHERE A.dateid=num_users.dateid-7) AS uc7,
(SELECT user_count FROM num_users AS A WHERE A.dateid=num_users.dateid-14) AS uc14,
(SELECT user_count FROM num_users AS A WHERE A.dateid=num_users.dateid-30) AS uc30
FROM num_users
ORDER BY num_users.dateid DESC;
But maybe you really want:
SELECT Sum(num_users.user_count) AS uc,
Sum(IIf([dateid]<=#12/31/2014#-7,[user_count],0)) AS uc7,
Sum(IIf([dateid]<=#12/31/2014#-14,[user_count],0)) AS uc14,
Sum(IIf([dateid]<=#12/31/2014#-30,[user_count],0)) AS uc30
FROM num_users;
Above tested with Access. If data actually continues through current date, replace #12/31/2014# with Date(). Formatting literal date and function will most likely be different in another database platform.