Aggregating data is a common task in SQL Server, especially when dealing with large datasets. In this article, we will explore different approaches to aggregating data using T-SQL.

Let’s consider a scenario where we have a table called StudentsEnroll with three columns: Class1, Class2, and Class3. Each column represents the number of students enrolled in a particular class. Our goal is to calculate the total number of students in each class.

One approach to solving this problem is by using a cursor. The cursor allows us to iterate over each row in the table and perform calculations. Here’s an example:

DECLARE @Class1 INT, @Class2 INT, @Class3 INT, @Class1Total INT, @Class2Total INT, @Class3Total INT
SET @Class1Total = 0
SET @Class2Total = 0
SET @Class3Total = 0

DECLARE student_CURSOR CURSOR FOR
SELECT Class1, Class2, Class3 FROM StudentsEnroll

OPEN student_CURSOR
FETCH NEXT FROM student_CURSOR INTO @Class1, @Class2, @Class3

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Class1Total = @Class1Total + @Class1
    SET @Class2Total = @Class2Total + @Class2
    SET @Class3Total = @Class3Total + @Class3

    FETCH NEXT FROM student_CURSOR INTO @Class1, @Class2, @Class3
END

CLOSE student_CURSOR
DEALLOCATE student_CURSOR

SELECT 'Class1 has ' + CAST(@Class1Total AS VARCHAR(10)) + ' students'
UNION ALL
SELECT 'Class2 has ' + CAST(@Class2Total AS VARCHAR(10)) + ' students'
UNION ALL
SELECT 'Class3 has ' + CAST(@Class3Total AS VARCHAR(10)) + ' students'

Another approach is to use the SUM function along with the GROUP BY clause. This allows us to calculate the sum of each class in a single query. Here’s an example:

SELECT 'Class1 has ' + CAST(SUM(CAST([Class1] AS INT)) AS VARCHAR(10)) + ' students.\n' +
       'Class2 has ' + CAST(SUM(CAST([Class2] AS INT)) AS VARCHAR(10)) + ' students.\n' +
       'Class3 has ' + CAST(SUM(CAST([Class3] AS INT)) AS VARCHAR(10)) + ' students.\n'
FROM [StudentsEnroll];

There are other creative solutions as well, such as using XML or temporary tables. The choice of solution depends on the specific requirements and constraints of your project.

It’s important to note that understanding these different approaches to aggregating data in SQL Server can greatly enhance your problem-solving skills and increase your chances of success in job interviews.

Which solution is your favorite? Let us know in the comments below!

Make sure to check out our other articles on SQL Server concepts and interview questions. And if you’re looking for a job in the SQL Server field, be sure to visit our job board for the latest opportunities.