Alternative for count()

Question

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?

Answer 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.

Answer 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