Query that only selects unique data-CodePudding

I just can't make a request that will count only unique records.
There is a table: bbs with columns:

to_id - to whom
from_id - from whom

SELECT COUNT(*) AS `count` FROM `bbs` WHERE `to_id`=? OR `from_id`=?

You need, for example, this data:

from_id  to_id
1          2
2          1
1          3
1          4
1          2

That is, for example, we are 1.
And it should count not 5, as it currently thinks, but 3.
You need to select 2 columns, only unique ones.
How to do this?

CodePudding user response：

I'm guessing that you mean something like the following.

select count(distinct(FROM_ID   TO_ID)) as "COUNT"
  from BBS
 where FROM_ID = 1
    or TO_ID = 1

That query will return 3, using the sample data in your question.
Refer to this db<>fiddle.

I'm also guessing that the question marks in the [SQL] query in your question are place holders for a particular ID and you use an example value of 1 (one) in your question. Hence the where clause in my [SQL] query, above.

CodePudding user response：

Something like this:

SELECT COUNT(*)
  FROM (SELECT DISTINCT LEAST(from_id, to_id), GREATEST(from_id, to_id) FROM bbs) AS xx
;