CODEWITHSUNDEEP
mysqlgroup-by
Steve
Steve

Reputation: 2588

Group rows based on time intervals in MySQL

I am trying to make categories through the row values.

Here are the rows:

id is just the unique ID, uid is User Id and timespent is the time spent on the site by the user in seconds

id  |uid | timespent
--------------------
133 | 41 | 2695
3   | 43 | 49920
45  | 73 | 95
83  | 91 | 5
29  | 109 | 2365
123 | 133 | 70
9   | 431 | 22010
2   | 445 | 15065
97  | 449 | 455
113 | 453 | 3140
5  | 455 | 44975
11 | 463 | 2695
13 | 465 | 5555
15 | 467 | 1775
17 | 469 | 55
19 | 471 | 465
21 | 473 | 380
23 | 475 | 280
25 | 477 | 3165
27 | 479 | 550
31 | 481 | 1530
33 | 483 | 2695
35 | 485 | 125
37 | 487 | 235
39 | 489 | 2120
41 | 493 | 10
43 | 495 | 2735
47 | 497 | 2700
49 | 499 | 2630
51 | 501 | 70
53 | 503 | 2240
55 | 505 | 15
57 | 507 | 5
59 | 509 | 3185
61 | 511 | 555
63 | 513 | 13480

What I am trying to create different categories of times, in which all these timespent entries would be adjusted.

Here is the picture of what I am trying to do: enter image description here

Any idea how I can achieve that?

Upvotes: 0

Views: 57

Answers (1)

Moneer Kamal
Moneer Kamal

Reputation: 1877

here try something like this

select 
 sum(case when (timespent/60)<15 THEN 1 ELSE 0 END) '<15',
 sum(case when (timespent/60)>15 and (timespent/60)<30 THEN 1 ELSE 0 END) '15-30',
 from time_table;

Upvotes: 2

Related Questions