Report - Stored Procedure 3

[rajkum]

Hi,
I have a requirement to create a report from a table which looks as under:

id
subject
type
sex


id is the id of a person.
subject is the subject the person is interested (subject1,subject2,subject3..)
type is the subject type (type1,type2,type3,type4)
sex is either male or female.

I want to create a report as below:

Total number of persons interseted in type1
Total number of persons interseted in type2
Total number of persons interseted in type3
Total number of persons interseted in type4

Total number of persons interseted in subject1
Total number of persons interseted in subject2
Total number of persons interseted in subject3
Total number of persons interseted in subject4
Total number of persons interseted in subject5
.
.
.
Total number of Male interested in subject1
.
.
.
Total number of Male interested in type1
.
.
Also .total number of female interested in subject1 and type 3 etc.
.
.
My Question is :
1. What is the best way to generate a report.
I want to use Stored Procedure.
I will be using this on the Web.

Thanks
raj.

 

[nigelrivett]

select sum(case when type = 'type1' then 1 else 0 end) ,
sum(case when type = 'type2' then 1 else 0 end) ,
....
sum(case when type = 'type1' and sex = 'male' then 1 else 0 end)
....
from tbl


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rajkum]

Thanks nigelrivett!!