What can I use other than Group By?

Question

I have a question that uses this DML statement

SELECT SupplierID, COUNT(*) AS TotalProducts
FROM Products
GROUP BY SupplierID;

I'm trying to get the same results without using "Group By". I can use a table variable or a temp table, with Insert and Update if needed. Also, using While and IF-Else is allowed.

I'm really lost any help would be awesome. Thanks SO Community.

This is used in SQL Server. Thanks again.

Answer 1

You can always use SELECT DISTINCT with window functions:

SELECT DISTINCT SupplierID,
       COUNT(*) OVER (PARTITION BY SupplierId) AS TotalProducts
FROM Products;

But GROUP BY is the right way to write an aggregation query.