Group value by ranges

hey guys,

Please help me out with the isue below:

I have a table with teh following fields

---------------------------
CID : OBJID : OBJPER:
---------------------------
1 1 50
1 2 70
1 3 40
1 4 90
1 5 50
1 6 55
1 7 30
1 8 49
-----------------------------------------------

another table Reports
-----------------------------------------------
CID: OBJPER<50 : OBJPER btw50-70 : OBJPER>70 :
-----------------------------------------------
1 3 4 1
------------------------------------------------

The first table contains data about a course and , each topic in it and the percetage

of students who answered them correctly

table 2 is a report table which contains the percentages separated into 3 categories.

can someone help me with sql query to do so ?


Thank you.

Menon

U can use pivot method to do it as follows


select CID,[1] as "OBJPER<50",[2] as "OBJPER btw50-70",[3] as "OBJPER>70" from (

select cid,1 as "range",count(*) as "count" from stu_tab where objper<50 group by cid
union all
select cid,2,count(*) from stu_tab where objper between 50 and 70 group by cid
union all
select cid,3,count(*) from stu_tab where objper>70 group by cid
)as p pivot(sum(count) for range in ([1],[2],[3])) as pvt order by cid