CODEWITHSUNDEEP
sqlpostgresql
Dracula2507
Dracula2507

Reputation: 35

Count if the data is not unique

I have a table of store information describing which stores are linked with one another. The data might look like this:

| store_id | link_num | linked_store |
|　　　1　  |     1    |     10       |
|　　　1　  |     1    |     10       |
|　　　1　  |     2    |     11       |
|　　　1　  |     3    |     12       |
|　　　1　  |     3    |     13       |
|　　　1　  |     4    |     14       |

I want to check if there is a store linked to different stores AT THE SAME link_num. Is it possible to have a query that will output something like below?

| store_id | link_num |  count | check  |
|　　　1　  |     1    |    2   | same   | 
|　　　1　  |     2    |    1   | (null) |
|　　　1　  |     3    |    2   | diff   |
|　　　1　  |     4    |    1   | (null) |

Any help is appreciated. Thank you

Upvotes: 1

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

count(distinct) can be relatively expensive relative to simpler aggregation functions. If you have a lot of data, you might compare this to:

select store_id, link_num,
       (case when min(linked_store) <>max(linked_store) then 'diff'
             when count(*) > 1 then 'same'
        end) as check_column
from t
group by store_id, link_num

Upvotes: 2

user330315
user330315

Reputation:

You can use count(distinct ..) for this:

select store_id, link_num, count(*) as count,
       case 
          when count(distinct linked_store) = 1 and count(*) > 1 then 'same' 
          when count(distinct linked_store) > 1 and count(*) > 1 then 'diff' 
       end as "check"
from the_table
group by store_id, link_num;

Upvotes: 5

Related Questions