CODEWITHSUNDEEP
phpmysqlsql
Renderer
Renderer

Reputation: 51

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?

Upvotes: 1

Views: 108

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I would recommend using conditional logic for the COUNT(DISTINCT):

SELECT COUNT(DISTINCT CASE WHEN to_id = ? THEN from_id ELSE to_id END) AS `count`
FROM bbs
WHERE ? IN (to_id, from_id);

The logic here is quite clear. There are no strange arithmetic operations on ids -- and this will work on string ids as well. Nor does it require a subquery.

Upvotes: 1

Jon Armstrong
Jon Armstrong

Reputation: 4694

Something like this:

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

Upvotes: 1

Abra
Abra

Reputation: 20914

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.

Upvotes: 1

Related Questions