GROUP BY using ranges of values 3

[clarkin]

Hi all,

I've had a long look through the archives here for an answer to my problem with no luck. Here's the problem:

I have a table of users and a time difference between start time and end time in the system (measured in minutes). Eg (simplified):

UserNo TimeDifference
--------------- --------------
+01 0
+02 0
+05 1
+99 5
+200 12
+23434 12
+232 25

I want to SELECT from this table using GROUP BY TimeDifference. Eg:

SELECT TimeDifference, COUNT(UserNo) FROM #timedifftable GROUP BY TimeDifference ORDER BY TimeDifference

This would give
0 2
1 1
5 1
(and so on)

What I really want is to GROUP BY ranges of TimeDifference - so I want to count the amount of users in ranges of 10 minutes. eg, I want to get this result:

TimeDifference Users
0-9 4
10-19 2
20-29 1
and so on

The actual table is very big so I don't want to have to specify each range in the select - especially since I may need to change the ranges used etc.

Any help appreciated.

 

[tlbroadbent]

Would the following work for you?

SELECT
TimeRange=TimeDifference/10,
COUNT(UserNo)
FROM #timedifftable
GROUP BY TimeDifference/10
ORDER BY TimeRange Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[sunila7]

Hi,

Try this query.....

SELECT Grp, Count(userno) usercount
FROM
(select UserNo,Convert(varchar,TimeDifference-TimeDifference%10) + '-' +
Convert(varchar,TimeDifference-TimeDifference%10 +9) Grp
from #t) TBL
Group by grp


Sunil

 

[clarkin]

Thanks for the quick response, that did the trick perfectly. Here's the SQL I used in the end (only added an AS to yours)

SELECT TimeRange=TimeDifference/10, COUNT(UserNo) AS Users
FROM #timedifftable
GROUP BY TimeDifference/10
ORDER BY TimeRange

Giving (on my full table):

TimeRange Users
----------- -----------
0 522
1 143
2 74
3 49
4 42
5 17
6 18
7 12
8 6
9 6
10 1
11 2
12 4
15 1
16 2
19 1
51 1
61 1
73 1
75 1
78 1
87 1

In a perfect world TimeRange would show 0-9, 10-19, etc. As a follow-up question, how would you include NULLs as rows with a COUNT of 0? So in that result set TimeRange 13 would have Users 0.

 

[clarkin]

thanks sunila7 - used this SQL:

SELECT LEFT(grp, 10) AS TimeRange, COUNT(userno) AS usercount
FROM
(SELECT UserNo, CONVERT(varchar, TimeDifference - TimeDifference % 10) + '-' +
CONVERT(varchar, TimeDifference - TimeDifference % 10 + 9) AS grp
FROM #timedifftable) TBL
GROUP BY grp
ORDER BY grp

to get the same result except with the ranges appropriately named. However i'm having trouble getting it to order them correctly.. here's the result set when the above is run:

TimeRange usercount
---------- -----------
0-9 522
10-19 143
100-109 1
110-119 2
120-129 4
150-159 1
160-169 2
190-199 1
20-29 74
30-39 49
40-49 42
50-59 17
510-519 1
60-69 18
610-619 1
70-79 12
730-739 1
750-759 1
780-789 1
80-89 6
870-879 1
90-99 6

.. which is clearly the correct way to order varchars representing numbers... I'd like to "ORDER BY TimeDifference/10". Suggestions?

 

[sunila7]

Hi,

try this query......

SELECT Grp, Count(userNO) usercount,No
FROM
(select UserNo,Convert(varchar,TimeDifference-TimeDifference%10) + '-' +
Convert(varchar,TimeDifference-TimeDifference%10 +9) Grp,TimeDifference/10 No
from #t) TBL
Group by grp,No

Sunil

 

[sunila7]

Hi,

u can also try this query............


select count(UserNo) usercount,Convert(varchar,TimeDifference-TimeDifference%10) + '-' +
Convert(varchar,TimeDifference-TimeDifference%10 +9) Grp,TimeDifference/10 No
from #t
group by
TimeDifference/10,
Convert(varchar,TimeDifference-TimeDifference%10) + '-' + Convert(varchar,TimeDifference-TimeDifference%10 +9)
order by
TimeDifference/10


Sunil

 

[clarkin]

thanks Sunila7, your final post was most useful.

For others looking for a similar solution here is the SQL I used in the end:

SELECT
LEFT(CONVERT(varchar,TimeDifference-TimeDifference%10) + '-' + CONVERT(varchar,TimeDifference-TimeDifference%10 +9), 10) AS Range,
COUNT(UserNo) AS UserCount,
TimeDifference/10 AS OrderNo
FROM #timedifftable
GROUP BY
TimeDifference/10,
LEFT(CONVERT(varchar,TimeDifference-TimeDifference%10) + '-' + CONVERT(varchar,TimeDifference-TimeDifference%10 +9), 10)
ORDER BY
TimeDifference/10

Which gave these results:

Range UserCount OrderNo
---------- ----------- -----------
0-9 534 0
10-19 144 1
20-29 75 2
30-39 48 3
40-49 45 4
50-59 17 5
60-69 18 6
70-79 12 7
80-89 6 8
90-99 6 9
100-109 2 10
110-119 3 11
120-129 4 12
150-159 1 15
160-169 2 16
190-199 1 19
510-519 1 51
610-619 1 61
730-739 1 73
750-759 1 75
780-789 1 78
870-879 1 87

Perfect.