I have a table 'Customers' in my database that includes the following:

CustomerID | SupplierID

Each customer is inserted to the database with a supplier , meaning a specific customer could be listed more than once.

I have a specific customer who has CustomerID=X

I'm trying to write a query in SQL that returns a table of the customers who satisfy the equation: number of mutual suppliers between the user and X > 80% of number of suppliers of customer X.

I succeeded to get a table of :

CustomerID1 | CustomerID2 | Num of Mutual Suppliers

where CustomerID1=X in all rows , but I couldn't include number of suppliers that customer X has in order to select only those who satisfy the condition.

select user1
     , user2
     , num_in_common
     , COUNT(*)
  from (select f1.CustomerID as user1, f2.CustomerID as user2, count(*) as num_in_common
from Customers f1 inner join
     Customers f2
     on f1.SupplierID = f2.SupplierID
group by f1.CustomerID, f2.CustomerID)
where user1 = X;

I expected to receive a table as following:

CustomerID1 | CustomerID2 | Num of Mutual Suppliers | Num of X's Suppliers

but I got the error 'You tried to execute a query that does not include the specified expression 'user1' as part of an aggregate function'.

2 Answers

Gordon Linoff On

I don't think the previous answer was correct, so I'm just providing a more accurate answer, now that the OP has clarified the question.

select x.customerid, c.customerid,
       (count(*) / num_x) as ratio
from customers c cross join
     (select @X as customerid, count(*) as num_x
      from customers c
      where c.customerid = @X
     ) x
where exists (select 1
              from customers cx
              where cx.supplierid = c.supplierid and
                    cx.customerid = @X
group by c.customerid, x.num_x
having count(*) / x.num_x >= 0.8
order by ratio desc;

Here is a demonstration (without data) that the code is syntactically correct.

Here is a demonstration that is works on mock data.

forpas On

With a self join of the table and then group by to aggregate:

  c.customerid CustomerID1, 
  m.customerid CustomerID2, 
  count(*) num_in_common,
  (select count(*) from customers where customerid = c.customerid) totalofX
from customers c inner join customers m
on m.customerid <> c.customerid and m.supplierid = c.supplierid 
where c.customerid = X 
group by c.customerid, m.customerid
having num_in_common > 0.8 * totalofX

See the demo.