How to calculate various sum columns based on value of another in SQL?

Question

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

You need equality conditions in the WHERE clause. Try again. — Gordon Linoff
– Gordon Linoff, Commented Jun 30, 2021 at 2:12
@Gordon Linoff So for instance, if I did the following, I still don't get the expected result. Perhaps I am referencing the datetime wrong? 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 = dateid - 7) AS uc7 FROM num_users — Techno04335
– Techno04335, Commented Jun 30, 2021 at 2:18

June7 · Accepted Answer · 2021-06-30 06:02:47Z

1

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.

edited Jun 30, 2021 at 6:02

answered Jun 30, 2021 at 3:40

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Techno04335 Over a year ago

Thanks! you are correct I did not add a tag on the database used. (alias)

Collectives™ on Stack Overflow

How to calculate various sum columns based on value of another in SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related