SQL Query to compare records having count > 1

Question

I have a temporary tabled named as Temp in a subquery. Within this table I have user_id, created_on and answer columns. The answer column contains only yes or no.

I would like to:

find if a user has different answers for different created_on dates, if that is the case then compare all those answers to find if the user has ever answered 'yes'

the table that I have looks like:

| user_id | created_on | answer |

|       1 | 12/7/2016  | no     |

|       1 | 12/6/2016  | no     |

|       1 | 12/5/2016  | yes    |

|       2 | 11/30/2016 | no     |

|       2 | 11/29/2016 | no     |

|       3 | 10/1/2016  | yes    |

|       4 | 9/2/2016   | no     |

The output should look like:

| user_id | final_answer |

|    1    |     yes      |

|    2    |     no       |

|    3    |     yes      |

|    4    |     no       |

I can think of Self Join to over come this problem but there are cases where the user_id count is 10. For cases which only have a single user_id entry the output should return just that single record from the answer column. How can this problem be tackled with a SQL Query?

If I understand you question properly you want only the final answer a user provides for any given question. I would first do a group by user id, question id and chose the max date for each userid/questionid. Having established the newest date for each userid/questionid then do a join against the main table where the userid/questionid and max dates match. — mba12
– mba12, Commented Dec 7, 2016 at 19:47
@mba12 applogies for the explanation. but I want to compare the answers for a particular user and if he/she has ever answered yes then the final column should contain a yes. Essentially the createn_on date doesn't matter, I'm just using it to list the different answers — Sravee
– Sravee, Commented Dec 7, 2016 at 19:55

SQLChao · Accepted Answer · 2016-12-07 19:56:09Z

1

Try this. Assign a 1 or 0 depending on whether they answered yes or no. Take the sum of this. If its greater than 0 that means they have to have answered yes at some point.

SELECT 
  user_id,
  CASE 
    WHEN(SUM(CASE WHEN answer = 'yes' THEN 1 ELSE 0 END)) > 0 THEN 'yes' 
    ELSE 'no' 
  END AS final_answer
FROM
  YourTempTable
GROUP BY user_id

answered Dec 7, 2016 at 19:56

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

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

4 Comments

SQLChao Over a year ago

You'll also want to be sure there aren't any other values other than yes or no. The above code will assign a 0 to anything that isn't 'yes'. @sravee

Sravee Over a year ago

Yes in my case the answers are only yes or no. But hypothetically if I had 7 different answers, I would have to play with the counts right?

SQLChao Over a year ago

If you had 7 different answers but you only care about yes the logic wouldn't change. Set all answers that aren't yes to 0.

Sravee Over a year ago

I meant the answers are not in the form or 'yes' or 'no'. Say for example the answers are 7 different numbers that a user can select.

Collectives™ on Stack Overflow

SQL Query to compare records having count > 1

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related