product_id AS product,
(SELECT TOP 1 client_id FROM Bill_Item, Bill
WHERE Bill_Item.product_id = p.product_id
and Bill_Item.bill_id = Bill.bill_id
) AS client
FROM Product p
I was going to submit pretty much the same thing as @Squirrell only with a Common Table Expression [CTE] rather than a derived table. So I wont duplicate that but there are some learning points concerning your query. First is
IMPLICIT JOINS such as
FROM Bill_Item, Bill are really easy to have uintended consequences (one of many questions: Queries that implicit SQL joins can't do?) Next for the Calculated column you can actually do this in a
OUTER APPLY or
CROSS APPLY which is a very useful technique.
So you could re-write your method as follows:
SELECT * FROM Product p OUTER APPLY (SELECT TOP 1 b.client_id FROM Bill_Item bi INNER JOIN Bill b ON bi.bill_id = b.bill_id WHERE bi.product_id = p.product_id GROUP BY b.client_id ORDER BY COUNT(*) DESC) c
And to show you how squirell's answer can still include products that have never been sold all you need to do is join Products and LEFT JOIN to other tables:
;WITH cte AS ( SELECT p.product_id ,b.client_id ,ROW_NUMBER() OVER (PARTITION BY p.product_id ORDER BY COUNT(*) DESC) as RowNumber FROM Product p LEFT JOIN Bill_Item bi ON p.product_id = bi.product_id LEFT JOIN Bill b ON bi.bill_id = b.bill_id GROUP BY p.product_id ,b.client_id ) SELECT * FROM cte WHERE RowNumber = 1
Techniques used in some of these that are useful.