query
-
Hi,
I have a table as shown below
col1 col2
1 16
2 24
3 28
3 32
5 40
6 42
i need a query that will display sum of col1 in the rage 15-25,26-35,36-45
for examples
2,15-25
2,26-35
2,36-45
How can i write the query in sql server2000
Thanks in advance
emamuthu
-
First, you don't want SUM. You want to COUNT the number of rows, not add them.
This is untested.....
SELECT (SELECT COUNT(Col1) WHERE Col1 BETWEEN 15 AND 25) AS '15-25',
(SELECT COUNT(Col1) WHERE Col1 BETWEEN 26 AND 35) AS '26-35',
(SELECT COUNT(Col1) WHERE Col1 BETWEEN 36 AND 45) AS '36-45'
FROM tablename
-SQLBill
-
It works if u add group by clause. thanks this what i am looking for.
Thanks
emamuthu
-
Hi all,
I suspect (i.e. know
) this is overkill - and particularly since you seem to have an answer you're happy with - but here are a few different ways of getting the kind of thing you're after. I would lean towards the final one from a flexibility point of view.--data
declare @t table (col1 int, col2 int)
insert @t
select 1, 16
union all select 2, 24
union all select 3, 28
union all select 3, 32
union all select 5, 40
union all select 6, 42
--calculation (horizontal)
select
sum(case when col2 BETWEEN 15 AND 25 then 1 else 0 end) AS '15-25',
sum(case when col2 BETWEEN 26 AND 35 then 1 else 0 end) AS '26-35',
sum(case when col2 BETWEEN 36 AND 45 then 1 else 0 end) AS '36-45'
from @t
--calculation (vertical)
select '15-25' as MyGroup, count(*) as Count from @t where col2 BETWEEN 15 AND 25 union all
select '26-35', count(*) from @t where col2 BETWEEN 26 AND 35 union all
select '36-45', count(*) from @t where col2 BETWEEN 36 AND 45
--calculation (vertical - more flexible)
declare @MyGroup table (x int, y int)
insert @MyGroup
select 15, 25
union all select 26, 35
union all select 36, 45
select x, y, count(col2) as count from @t a inner join @MyGroup b on col2 between x and y group by x, y
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply