CODEWITHSUNDEEP
sqlsql-servercommon-table-expression
Steef
Steef

Reputation: 333

Add column like Excels vlookup

I am working on adding a column multi_company to the table underneath. One that tells whether a user exists in two or more companies. I have come up with a working snippet, but keep thinking there must be a better and faster way. 

customer     company      size      multi_company
justine      google       1         1
jack         google       5         0
grace        google       4         1
justine      microsoft    2         1
grace        microsoft    3         1

Performance is a real issue here, since the dataset will be large and this automated process should run often every day. Can anyone come up with a better way of solving this?

create table #1(customer varchar(255), company varchar(255), size int)
insert into #1(customer, company, size) values('justine', 'google', 1)
insert into #1(customer, company, size) values('jack', 'google', 5)
insert into #1(customer, company, size) values('grace', 'google', 4)
insert into #1(customer, company, size) values('justine', 'microsoft', 2)
insert into #1(customer, company, size) values('grace', 'microsoft', 3)
;
with cte as (
select customer
     , count(1) as multi_company 
  from #1 
 group by customer 
   having count(1) > 1
)
select #1.*
     , case when cte.multi_company is not null 
            then 1 
            else 0 end as multi_company 
  from #1 
  left join cte on #1.customer = cte.customer

Upvotes: 0

Views: 74

Answers (2)

Jonathan
Jonathan

Reputation: 349

I think that you are almost there. You just need to simplify it a bit:

select
  c.*,
  m.multi_company
from
  #1 c
  join
  (
    select
      customer,
      case
        when count(*) > 1 then 1
        else 0
      end as multi_company
    from #1
    group by customer
  ) m on c.customer = m.customer;

I get this output:

CUSTOMER        COMPANY         MULTI_COMPANY
--------------- --------------- -------------
justine         microsoft                   1
justine         google                      1
grace           microsoft                   1
grace           google                      1
jack            google                      0

Upvotes: 1

saravanatn
saravanatn

Reputation: 630

Will this help ???

SELECT
  customer,
  company,
  size,
  CASE
    WHEN COUNT(customer) OVER (PARTITION BY customer ORDER BY customer) >1 THEN 1
    ELSE 0
  END AS multi_company
FROM cust

Upvotes: 0

Related Questions