Group or cross function-CodePudding

I have a table with some data

ID  Topic
1   A
1   B
2   B
3   C
4   A
4   B
5   A

Total 5 records

I like to count when same ID with Topic A then count B as well. So the result count total 5 ID2 = 2 (A and B) and ID4 = 2 (A and B) and ID5 = 1 (A)
Not sure how to count like this. Thank you

CodePudding user response：

As @SMor mentioned, this question is not clear.

If you only know the table above and the result is 5, and don't know the logic for calculating the result, we can think of it as getting the result to get the number of unique IDs in this table.

Because there is only one logic to get the value of 5 in the table above.

If so, you can try the following.

  SELECT count(id) result
  FROM (
      select id
      from your_table
      group by id
  ) A

Or If you want to just get 5 records (any topic about the same ID) then try this.

SELECT id, max(topic)
FROM your_table
GROUP BY id