What can I use other than Group By?

user_8889542 0
Viewed 112

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.

group-by tsql sql-server sql
4 Answers

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.

user_11440350

You may 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!

user_56959530

Using a subquery:

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

The distinct is to remove duplicates... the count executes for every row so without distinct you would get repeat answers for supplierID.

user_30643450

Another way

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