SQL Query Help, Distinct and Group By

Question

Hello,

This should be an easy question but I'm relatively new to SQL.

I have a table of products (actually a view) with about 500,000 rows. Amongst other columns I have [Supplier Name] and Brand.

I have 301 distinct [Supplier Name] and I have 1602 distinct Brand.

Most brands are only held by one supplier, but 114 of them are held by more than one supplier and the total non-distinct number of suppliers across those 114 brands is 261.

I need a query that will result in two columns, [Supplier Name] and Brand where each brand is represented at least once alongside the supplier that holds it. Where a brand is held by more than one supplier, I want a row for each supplier that holds that brand. It will end up being around 1750 rows I expect.

I have made this query to get distinct Brands and the number of suppliers, but I want to expand this out to include the supplier names:

Select * from (Select count (distinct [Supplier Name]) as NumberOfSuppliers ,Brand from [Members View] group by Brand  
) as x where NumberOfSuppliers > 1  
 order by NumberOfSuppliers desc, Brand asc   

The reason for this is I want to provide a filter in an application where the user can select the supplier in a drop down then another drop down for brand is filtered to only show the brands that supplier holds, and vice versa. I can do this in code but because the app is a Power App, I'm limited to 2000 rows because Distinct() is not a delegable function in Power Apps and I get incomplete results. I therefore need to do this in SQL.

Answer 1

SELECT [Supplier Name], [Brand], [NumberOfSuppliers]
FROM (
    SELECT [Supplier Name], [Brand], COUNT(DISTINCT [Supplier Name]) OVER(PARTITION BY [Brand] ORDER BY NEWID()) AS [NumberOfSuppliers]
    FROM [Members View]
    GROUP BY [Supplier Name], [Brand]
) AS temp
WHERE [NumberOfSuppliers] > 1
ORDER BY [NumberOfSuppliers] DESC, [Brand] ASC;

Answer 2

Hi @Will Page ，

I tried the following methods, but there is no test data, and I am not sure whether it works. If it does not solve your problem, please share your create and insert statement with us.

    ;with cte  
    as (Select count (distinct [Supplier Name]) as NumberOfSuppliers ,Brand   
    from [Members View]  
    group by Brand )   
      
    select c.NumberOfSuppliers,m.[Supplier Name],c.Brand  from cte c  
    join [Members View] m on c.Brand=m.Brand  
    where NumberOfSuppliers > 1  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html