CODEWITHSUNDEEP
sqlcount
Sravee
Sravee

Reputation: 113

SQL Query to compare records having count > 1

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?

Upvotes: 0

Views: 132

Answers (1)

SQLChao
SQLChao

Reputation: 7847

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

Upvotes: 1

Related Questions