Select SQL with conditions to count different values

Question

I'm trying to do a query on SQL but I'm stuck!

I have two tables:
Table 1: Question
Table 2: Answer

For each question I can have one or more answers from different users but each user can comment one time.
When a user answers a question, he must choose one status to his answer:
1) Agree, 2) Disagree or 3) Discuss

So, the "Question" table has all the questions like this:

Id  Question
1   q1
2   q2
3   q3

..and the "Answer" table has all the answers from the users, plus the FK from the "Question" table and a column with the status chosen by the user.

Id  Answer  IdQuestion   Status
1   a1      1            1
2   a2      1            3
3   a3      2            2
4   a4      2            2
5   a5      3            1

What I need: I need to select all the questions AND I need to count all the questions that has different aswer's status.

Example:
Question 1 has two answers and the two answers has different status. I need to count or put a number just to know that this question has answers with diffent status.

Question 2 has two answers but all the answers has the same status. I don't need to count that.. or maybe put other number to differentiate from the questions that has answers with diffent status.

And the Questions that has only one answer I just select it normally.

post the sample output

mohan111
– mohan111

2015-08-05 13:25:56 +00:00
Commented Aug 5, 2015 at 13:25 — mohan111
– mohan111, Commented Aug 5, 2015 at 13:25

Tanner · Accepted Answer · 2015-08-05 13:47:02Z

1

Correct me if I'm wrong, but it sounds like you're just after a distinct Status count from the Answers table. So based on a question (regardless of answers) you want to count how many different Status values are present for each question:

CREATE TABLE #Question
    (
      [Id] INT ,
      [Question] VARCHAR(2)
    );

INSERT  INTO #Question
        ( Id, Question )
VALUES  ( 1, 'q1' ),
        ( 2, 'q2' ),
        ( 3, 'q3' ),
        ( 4, 'q4' )

CREATE TABLE #Answer
    (
      [Id] INT ,
      [Answer] VARCHAR(2) ,
      [IdQuestion] INT ,
      [Status] INT
    );

INSERT  INTO #Answer
        ( [Id], [Answer], [IdQuestion], [Status] )
VALUES  ( 1, 'a1', 1, 1 ),
        ( 2, 'a2', 1, 3 ),
        ( 3, 'a3', 2, 2 ),
        ( 4, 'a4', 2, 2 ),
        ( 5, 'a5', 3, 1 );

SELECT  q.id  ,
        COUNT(DISTINCT Status) DistinctStatusCount
FROM  #Question   q
LEFT JOIN #Answer a ON q.Id = a.IdQuestion
GROUP BY q.Id

DROP TABLE #Answer
DROP TABLE #Question

Output

IdQuestion  DistinctStatusCount
1           2
2           1
3           1
4           0

If you're only interested with questions that have at least one answer you can simply refer to the Answers table:

SELECT  IdQuestion ,
        COUNT(DISTINCT Status) DistinctStatusCount
FROM    #Answer
GROUP BY IdQuestion

edited Aug 5, 2015 at 13:47

answered Aug 5, 2015 at 13:39

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xQbert Over a year ago

What about questions without any answers per comment: "I need to select all the questions "

Tanner Over a year ago

@xQbert have updated my answer to cover both scenarios, thanks

Zohar Peled · Accepted Answer · 2015-08-05 13:37:05Z

0

try this:

select q.id, 
       question, 
       case when min(status) <> max(status) then 
          1 
       else 
          0 
       end as hasDifferentStatuses
from questions q
inner join answers a on(q.id = a.IdQuestion)
group by q.id, question

answered Aug 5, 2015 at 13:37

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Comments

xQbert · Accepted Answer · 2015-08-05 13:36:58Z

0

I must be missing something as this seems pretty straight forward... The only reason I included the join is a question may not have any answers and you asked for all questions to be returned. Otherwise this could be a query just on the answer table.

SELECT Q.id, count(Distinct A.status)
FROM Question Q
LEFT JOIN Answer A
 on Q.ID = A.IdQuestion
Group by Q.IdQuestion

answered Aug 5, 2015 at 13:36

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Collectives™ on Stack Overflow

Select SQL with conditions to count different values

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related