0

My table currently has 21000 records, it's daily updated and almost 300 entries are inserted. Now, what I want is to have a query which will fetch the counts of elements that my table had for the previous 10 days, so it returns:

26000

21300

21000

etc

Right now, I wrote this:

"SELECT COUNT(*) from tbl_task where `task_start_time` < '2020-12-01'"

And it returns 21000 but only for 1 day. I want by query to return records according to 10 days.

However, this does it for only 1 day. edit : database flavor is mysql and date column is date not datetime

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Salman Ahmed
  • 50
  • 10
  • [See if this helps](https://stackoverflow.com/q/27599557/681929) – nobalG Dec 26 '20 at 12:37
  • 1
    'f you use an aggregate function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.' - https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html – P.Salmon Dec 26 '20 at 12:53
  • Edit your question and specify the kind of database engine you're using, including the version, so people can give you an accurate answer. – Cristian Gonçalves Apr 10 '21 at 14:05

3 Answers3

1

The most efficient method may be aggregation and cumulative sums:

select date(task_start_time) as dte, count(*) as cnt_on_day,
       sum(count(*)) over (order by date(task_start_time)) as running_cnt
from tbl_task
group by dte
order by dte desc
limit 10;

This returns the last 10 days in the data. You can easily adjust to more days if you like -- in fact all of them -- without much trouble.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thankyou. sorry to bother but can u tell me how can i now add a variable instead of just hard code 10. this is out of scope for this question i know but what would be its syntax – Salman Ahmed Dec 26 '20 at 22:29
  • @SalmanAhmed . . . The syntax for `limit` explains what can be passed in: https://dev.mysql.com/doc/refman/8.0/en/select.html. – Gordon Linoff Dec 26 '20 at 23:00
0

You can use UNION ALL and date arithmetic.

SELECT count(*)
       FROM tbl_task
       WHERE task_start_time < current_date
UNION ALL
SELECT count(*)
       FROM tbl_task
       WHERE task_start_time < date_sub(current_date, INTERVAL 1 DAY)
...
UNION ALL
SELECT count(*)
       FROM tbl_task
       WHERE task_start_time < date_sub(current_date, INTERVAL 9 DAY);

Edit:

You might also join a derived table that uses FROM-less SELECTs and UNION ALL to get the days to look back and then aggregate. This might be a little easier to construct dynamically. (But it may be slower I suspect.)

SELECT count(*)
       FROM (SELECT 0 x
             UNION ALL
             SELECT 1
             ...
             UNION ALL
             SELECT 9)
            INNER JOIN tbl_task t
                       ON t.task_start_time < date_sub(current_date, INTERVAL x.x DAY)
       GROUP BY x.x;

In MySQL version 8+ you can even use a recursive CTE to construct the table with the days.

WITH RECURSIVE x
AS
(
SELECT 0 x
UNION ALL
SELECT x + 1
       FROM x
       WHERE x + 1 < 10
)
SELECT count(*)
       FROM x
            INNER JOIN tbl_task t
                       ON t.task_start_time < date_sub(current_date, INTERVAL x.x DAY)
       GROUP BY x.x;
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • but i want to make this dynamic so its upto user can filter number of days – Salman Ahmed Dec 26 '20 at 12:50
  • @SalmanAhmed: That's not what you've asked. Please don't change the question and invalidate answers. – sticky bit Dec 26 '20 at 12:55
  • i'm sorry, i forgot to mention it in question, your answer is correct according to current requirements of question. i guess i can workaround this answer and use loops to make it dynamic but if theres a better solution please do tell – Salman Ahmed Dec 26 '20 at 12:57
0

I don't know if I'm wrong, but could you not simple add a GROUP BY - statement? Like:

"SELECT COUNT(*) from tbl_task where `task_start_time` < '2020-12-01' GROUP 
BY task_start_time"

EDIT: This should only work if task_start_time is a date, not if it is a datetime

EDIT2: If it is a datetime you could use the date function:

SELECT COUNT(*) from tbl_task where `task_start_time` < '2020-12-01' GROUP 
BY DATE(task_start_time)
Alexander Klement
  • 112
  • 6