CODEWITHSUNDEEP
sql-server
Victor
Victor

Reputation: 762

Select SQL with conditions to count different values

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.

Upvotes: 0

Views: 68

Answers (3)

Tanner
Tanner

Reputation: 22753

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

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

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

Upvotes: 0

xQbert
xQbert

Reputation: 35343

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

Upvotes: 0

Related Questions