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'.