Skip to content
Advertisement

Query that only selects unique data

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?

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement