Grouping data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grouping data

Author

Topic

aberbotimue
Starting Member

26 Posts

Posted - 2010-10-04 : 16:28:35

Hi all..

I have a dataset with 120 or so values, but 1000 rows...

I can "group by" to get the 1000 rows into 120 values, but thats still a bit large a data set...

what i wanted to do was put thoses 120 values into groups of 10, and then count data in the bigger groups..

but i can't for the life of me figure it...

Any help would be greatful

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-04 : 18:04:39

From Brett's sig:

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 07:22:48

Do you want to apply Pagination?

Madhivanan

Failing to plan is Planning to fail

aberbotimue
Starting Member

26 Posts

Posted - 2010-10-05 : 16:27:36

sorry..

the table is two feilds

number - duration

some example data would be

1234 - 3
1234 - 4
1234 - 3
1234 - 4
1234 - 3
1234 - 4
1234 - 3
1234 - 10
1234 - 11
1234 - 4
1234 - 3
1234 - 4
1234 - 12
1234 - 14
1234 - 13
1234 - 24
1234 - 3
1234 - 24

what i got so far was

SELECT COUNT(Number) AS Expr1, Duration
FROM stats
WHERE (Number = 1234)
GROUP BY Duration

except this gives

expr1 , duration
6 3
5 4
1 10
1 11
1 12
1 13
1 14
2 24

what i am after is something that groups them

duration count
0 - 9 11
10 - 19 5
20 - 29 2

not sure what Pagination is, so don't know if thats what i want!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-05 : 17:10:48

try this:


select [number]
       ,convert(varchar(3), 10*([duration]/10)) 
              + ' - ' 
              + convert(varchar(3), 10*([duration]/10)+9) 
       ,count(number) [count]
from   stats
group by [number]
       ,[duration]/10
order by [number]
       ,[duration]/10

Be One with the Optimizer
TG

aberbotimue
Starting Member

26 Posts

Posted - 2010-10-08 : 16:15:44

Thanks..

I am getting this error..

Msg 8120, Level 16, State 1, Line 1
Column 'stats.Duration' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

when I remove the "/10" from the group by and the order by - it does work, but duplicates the rows that should be grouped..

7279 0 - 9 1
7279 0 - 9 1
7279 10 - 19 1
7279 10 - 19 1
47279 10 - 19 3

I have tried to tweak, but not good enough to figure it out..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-11 : 13:06:07

It works for me:


declare @t table (number int, duration int)
insert @t
select 1234,3 union all
select 1234,4 union all
select 1234,3 union all
select 1234,4 union all
select 1234,3 union all
select 1234,4 union all
select 1234,3 union all
select 1234,10 union all
select 1234,11 union all
select 1234,4 union all
select 1234,3 union all
select 1234,4 union all
select 1234,12 union all
select 1234,14 union all
select 1234,13 union all
select 1234,24 union all
select 1234,3 union all
select 1234,24


select [number]
       ,convert(varchar(3), 10*([duration]/10)) 
              + ' - ' 
              + convert(varchar(3), 10*([duration]/10)+9) 
       ,count(number) [count]
from   @t --stats
group by [number]
       ,[duration]/10
order by [number]
       ,[duration]/10


OUTPUT:
number                count
----------- --------- -----------
1234        0 - 9     11
1234        10 - 19   5
1234        20 - 29   2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources