query group by

have a table with records ranging from 0 to 100. some are repeated multiple
times, such as:
0
0
1
1
1
1
2
2
trying to figure out how to get a result that list the numbers and how many
times they each occur. such as.
num times
0 2
1 4
2 2
Any help would be appreciated. thanks,.
SELECT num, COUNT(*)
FROM table
GROUP BY num
ORDER BY num
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> have a table with records ranging from 0 to 100. some are repeated
multiple
> times, such as:
> 0
> 0
> 1
> 1
> 1
> 1
> 2
> 2
> trying to figure out how to get a result that list the numbers and how
many
> times they each occur. such as.
> num times
> 0 2
> 1 4
> 2 2
> Any help would be appreciated. thanks,.
|||I miswrote what I was looking for. Actually looking for the numbers of a
range. such as:
0 thru 9 22
10 thru 19 89
20 thru 29 34
or should this be done in a presentation layer.
thanks,
"Aaron [SQL Server MVP]" wrote:

> SELECT num, COUNT(*)
> FROM table
> GROUP BY num
> ORDER BY num
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Chad" <Chad@.discussions.microsoft.com> wrote in message
> news:04CD49DB-34C4-4248-A874-5AFE88F4994C@.microsoft.com...
> multiple
> many
>
>
|||If you have a finite set of numbers, you could write some really elaborate
CASE logic. But I think this kind of thing is better done at the
presentation layer.
http://www.aspfaq.com/
(Reverse address to reply.)
"Chad" <Chad@.discussions.microsoft.com> wrote in message
news:A7B99A2F-BF8A-46D0-B85F-78215711294A@.microsoft.com...
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
|||That can easily be done by applying integer division. In the query
below, NumRange represents the lower bound of the range.
create table #t (num int,val int)
insert into #t values (0,1)
insert into #t values (1,1)
insert into #t values (9,2)
insert into #t values (10,3)
insert into #t values (15,8)
SELECT (num/10)*10 NumRange,SUM(val)
FROM #t
GROUP BY (num/10)*10
ORDER BY NumRange
drop table #t
Gert-Jan
Chad wrote:[vbcol=seagreen]
> I miswrote what I was looking for. Actually looking for the numbers of a
> range. such as:
> 0 thru 9 22
> 10 thru 19 89
> 20 thru 29 34
> or should this be done in a presentation layer.
> thanks,
> "Aaron [SQL Server MVP]" wrote: