CODEWITHSUNDEEP
sql-server-2008-r2
Niha
Niha

Reputation: 1

how to combine multiple query into one single query

I have three queries as below and I need to combine them into one. Does any body know how to do that?

select COUNT(*) from dbo.VWAnswer where questionId =2 and answer =1

select COUNT(*) from dbo.VWAnswer where questionId =3 and answer =4

select COUNT(*) from dbo.VWAnswer where questionId =5 and answer =2

I want to find out total count of those people whose gender = 1 and Education = 4 and marital status = 2

Following is the table columns(With one ex) that i refer:

questionId      questionText    anwser    AnserSheetID

  1              Gender           1           1

  2              Qualification    4           1

  3              Marital Status   2           1

  1              Gender           2           2

  2              Qualification    1           2

  3              Marital Status   2           2

  1              Gender           1           3

  2              Qualification    3           3

  3              Marital Status   1           3

Basically, these are questions answered by different people whose answers are stored in this table.

So if we consider above table entries I should get 1 as total count based upon above 3 conditions i.e. gender = 1 and Education = 4 and marital status = 2

Can someone tell me what I need to do to get this to work?

Upvotes: 0

Views: 47

Answers (2)

bummi
bummi

Reputation: 27384

You might use a joined view meeting you conditions and select the count of the rows fitting your conditions.

Select COUNT(*) as cnt from
(
   Select a.AnserSheetID
   from VWAnswer a
   Join VWAnswer b on a.AnserSheetID=b.AnserSheetID and b.questionId = 2 and b.anwser=4
   Join VWAnswer c on a.AnserSheetID=c.AnserSheetID and c.questionId = 3 and c.anwser=2
   where a.questionId=1 and a.anwser=1
) hlp

Upvotes: 0

MANOJ GOPI
MANOJ GOPI

Reputation: 1279

If you want to combine your three count queries, you can try the below SQL to get it done. 

select 
sum(case when questionId =2 and anwser=1 then 1 else 0 end) as FCount,
sum(case when questionId =3 and anwser=4 then 1 else 0 end) as SCount,
sum(case when questionId =5 and anwser=2 then 1 else 0 end) as TCount
from dbo.VWAnswer

Update 1:

select 
Sum(case when questionText='Gender' and anwser='1' then 1 else 0 end) as GenderCount,
Sum(case when questionText='Qualification' and anwser='4' then 1 else 0 end) as EducationCount,
Sum(case when questionText='Marital Status' and anwser='2' then 1 else 0 end) as MaritalCount
from VWAnswer

We can only get the counts based on the rows and every condition should apply in each row.

Upvotes: 2

Related Questions