Query database to obtain first unique value

Question

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

Shidersz · Accepted Answer · 2018-12-08T04:59:30.413

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

score 0 · Answer 2 · edited Dec 11 '18 at 11:13

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.

edited Dec 11 '18 at 11:13

answered Dec 06 '18 at 22:11

cristian hantig

1,110
1
12
16

I have just tried that but it is still returning duplicates for custom_id – katie hudson Dec 06 '18 at 22:17
The only difference between `count(*)` and `count(answer)` is if there are null answers, then `count(answer)` will not count those. – Barmar Dec 06 '18 at 22:36
See https://stackoverflow.com/questions/2876909/count-and-countcolumn-name-whats-the-diff – Barmar Dec 06 '18 at 22:36

Query database to obtain first unique value

2 Answers2