1

I have a pretty simple table like this:

id | custom_id | answer ---------------------------------- 1 | 123 | Answer 1 __________________________________ 2 | 123 | Answer 2 __________________________________ 3 | 422 | Answer 3 __________________________________ 4 | 345 | Answer 2 __________________________________ 5 | 992 | Answer 1 __________________________________ 6 | 452 | No answer __________________________________

What I am trying to do is count the number of Answer 1, Answer 2, etc. So, for the above data I would expect to get:

2 * Answer 1 2 * Answer 2 1 * Answer 3

Note, that anything that is No answer should be discarded.

However, further to the above, I want to take into account only one answer per custom_id, and this should be their first answer. So really, the output I expect to get for the above data should be:

2 * Answer 1 1 * Answer 2 1 * Answer 3

This is because we take only the first answer for custom_id = 123.

So far, I have made the following query:

select answer, count(*) as totalCount from " . DB_TABLE . " where answer <> 'No answer' group by custom_id

However, this seems to return the total counts (as I explained first), not taking into consideration that there should only be one per custom_id. I thought the group by would solve this issue, but this does not seem to be the case.

How can I achieve the results I am after?

Thanks

Improve this question

2 Answers 2

Reset to default
3

One approach, will be first to create a derived table with the IDs of the first answers for every custom_id and also filter those with values No answer (since you want to ignore they), like this:

SELECT custom_id, MIN(id) AS firstAnswerID FROM <table_name> WHERE answer <> "No Answer" GROUP BY custom_id

Then, we can join the original table with this previous one on the ID column (this will act like a filter for those that aren't first answers or have the No answer value), make a GROUP BY the answer column and count the numbers of each one. In summary, this will do what you want:

SELECT t1.answer, COUNT(*) AS NumTimes FROM <table_name> AS t1 INNER JOIN ( SELECT custom_id, MIN(id) AS firstAnswerID FROM <table_name> WHERE answer <> "No Answer" GROUP BY custom_id ) AS t2 ON t2.firstAnswerID = t1.id GROUP BY t1.answer ORDER BY NumTimes DESC

You can play with this here: DB Fiddle

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

Comments

0

Try to use this:

select answer, count(answer) as totalCount from " . DB_TABLE . " where answer <> 'No answer' group by answer

You should count rows for every answer group, not for the entire table.

Improve this answer

3 Comments

I have just tried that but it is still returning duplicates for custom_id
The only difference between count(*) and count(answer) is if there are null answers, then count(answer) will not count those.
See stackoverflow.com/questions/2876909/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.