Hi again,
First off, let me state that I've been told(and agree) that there should be some re-design of dBase, but:
I need a count of Married/Single/divorced Users who have ordered Product X, and all of the info is in one table:
TblAnswers(No Primary Key)
Ans_QuestionnaireID |----->Question Category
Ans_Question |------------>Question Number
Ans_AnsweredByUserID |---->UserID
Ans_String_Val |---------->Answer to Ans_Question
Ans_QuestionnaireID=001 |->Questions about Users
Ans_Question=12 |--------->Marriage Question
Ans_QuestionnaireID=345 |->Questions about Product X
Ans_Question=17 |--------->Order Confirmation
This is What I came up with:
SELECT
'Married'=SUM(CASE when m.Ans_String_Val=0 THEN 1 ELSE 0 END),
'Sinlge'=SUM(CASE when m.Ans_String_Val=1 THEN 1 ELSE 0 END),
'Divorced'=SUM(CASE when m.Ans_String_Val=2 THEN 2 ELSE 0 END)
FROM TblAnswers m, TblAnswers p
WHERE
m.Ans_AnsweredByUserID=p.Ans_AnsweredByUserID and
m.Ans_QuestionnaireID=001 and
m.Ans_Question=12 and
p.Ans_QuestionnaireID=345 and
p.Ans_Question=17;
SUM(CASE) code can is proven as per thread220-68808
SUM(CASE) code can be replaced with COUNT()
I know that there is a better way to Query this(such as INNER JOIN, but no Primary Key's), and since I'm on a live dBase(SQL server7.0), I'm afraid to run it.
Any Suggestions?, Michael
First off, let me state that I've been told(and agree) that there should be some re-design of dBase, but:
I need a count of Married/Single/divorced Users who have ordered Product X, and all of the info is in one table:
TblAnswers(No Primary Key)
Ans_QuestionnaireID |----->Question Category
Ans_Question |------------>Question Number
Ans_AnsweredByUserID |---->UserID
Ans_String_Val |---------->Answer to Ans_Question
Ans_QuestionnaireID=001 |->Questions about Users
Ans_Question=12 |--------->Marriage Question
Ans_QuestionnaireID=345 |->Questions about Product X
Ans_Question=17 |--------->Order Confirmation
This is What I came up with:
SELECT
'Married'=SUM(CASE when m.Ans_String_Val=0 THEN 1 ELSE 0 END),
'Sinlge'=SUM(CASE when m.Ans_String_Val=1 THEN 1 ELSE 0 END),
'Divorced'=SUM(CASE when m.Ans_String_Val=2 THEN 2 ELSE 0 END)
FROM TblAnswers m, TblAnswers p
WHERE
m.Ans_AnsweredByUserID=p.Ans_AnsweredByUserID and
m.Ans_QuestionnaireID=001 and
m.Ans_Question=12 and
p.Ans_QuestionnaireID=345 and
p.Ans_Question=17;
SUM(CASE) code can is proven as per thread220-68808
SUM(CASE) code can be replaced with COUNT()
I know that there is a better way to Query this(such as INNER JOIN, but no Primary Key's), and since I'm on a live dBase(SQL server7.0), I'm afraid to run it.
Any Suggestions?, Michael