'Alternative for count()

I have used the count() function to calculate each of the rows values 

select course, count(*) course_count from student_table
group by course;

Without using count() is there any alternative to accomplish this?

sql-server


Solution 1:[1]

You could use SUM(1) instead:

SELECT
    course,
    SUM(1) AS course_count
FROM student_table
GROUP BY
    course;

SUM(1) happens to behave the same way as count here, because it sums 1 for each record. However, COUNT in fact is the semantically correct function to use here.

Solution 2:[2]

You can also use the below way.

CREATE TABLE Account
( Account_id Int, AccountName varchar(20));

INSERT INTO Account (Account_id, AccountName) 
Values (1, 'Test'), (2, 'Test A'), (3, 'Test A'), (4, 'Test C'), (5, 'Test D')

Select AccountName, Max(CntTotal) as CntTotal from(
Select AccountName,
    ROW_NUMBER() OVER (Partition By AccountName Order By AccountName) as CntTotal
from Account
)a group by AccountName

Online Demo

Solution 3:[3]

If you have Adventure Work means, try this:

with CTE as
(
select *, ROW_NUMBER()over(order by PurchaseOrderID)as RR from Purchasing.PurchaseOrderDetail
)

select * from CTE where RR>=1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Suraj Kumar
Solution 3 Jeremy Caney

Related Questions