What can I use besides Group By?

Question

I have a question that uses this DML statement

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

I am trying to get the same results without using "Group By". I can use a table variable or a temporary table, if necessary, insert and update. In addition, While and IF-Else are allowed.

I really lost any help, that would be great. 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 record an aggregation request.

Answer 2

You can also use the following query:

 select distinct P.SupplierID, (select count(*) from Products where SupplierID=P.SupplierID) TotalProducts from Products P 

You will get the same result using the above query, but I don't think avoiding GROUP BY is a good idea!

Answer 3

Using a subquery:

 SELECT DISTINCT SupplierID ,(SELECT COUNT(*) FROM Products P2 WHERE P2.SupplierID = P.SupplierID ) AS TotalProducts FROM Products P 

The difference is the removal of duplicates ... the counting is done for each row, so without any differences you will get repeated answers for the provider identifier.

Answer 4

Another way

 select distinct supplierId, p2.ttl from products p1 cross apply ( select count(*) from products p2 where p1.supplierId = p2.supplierId ) p2(ttl);