# When is SUM Aggregation Function or Window function, and column order in SELECT

While studying window functions,
I ran these on https://sqliteonline.com/ using SQL Server

``````CREATE TABLE Students
(
Id INT PRIMARY KEY IDENTITY,
StudentName VARCHAR (50),
StudentGender VARCHAR (50),
StudentAge INT
)

INSERT INTO Students VALUES ('Sally', 'Female', 14 )
INSERT INTO Students VALUES ('Edward', 'Male', 12 )
INSERT INTO Students VALUES ('Jon', 'Male', 13 )
INSERT INTO Students VALUES ('Liana', 'Female', 10 )
INSERT INTO Students VALUES ('Ben', 'Male', 11 )
INSERT INTO Students VALUES ('Elice', 'Female', 12 )
INSERT INTO Students VALUES ('Nick', 'Male', 9 )
INSERT INTO Students VALUES ('Josh', 'Male', 12 )
INSERT INTO Students VALUES ('Liza', 'Female', 10 )
INSERT INTO Students VALUES ('Wick', 'Male', 15 )
``````
``````SELECT Id, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (ORDER BY Id) AS UngroupedRunningAgeTotal,
SUM (StudentAge) OVER (PARTITION BY StudentGender ORDER BY Id) AS RunningAgeTotal,
SUM (StudentAge) OVER (PARTITION BY StudentGender) AS GroupAgeTotal
FROM Students
``````

Is it correct to say SUM() is an Aggregation function when matched to a `GROUP BY` and a Window function when matched to `OVER` clause?

From this exercise, I realized if ORDER BY exists, SUM() becomes a running total rather than duplicated (when no ORDER BY).

What other window functions change behaviour like that? (sensitive to ORDER BY clause).

If I move the 1st column selected to the 3rd position:

``````SELECT Id, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (PARTITION BY StudentGender ORDER BY Id) AS RunningAgeTotal,
SUM (StudentAge) OVER (PARTITION BY StudentGender) AS GroupAgeTotal,
SUM (StudentAge) OVER (ORDER BY Id) AS UngroupedRunningAgeTotal
FROM Students
``````

the results are no longer ordered by ‘Female’, ‘Male’ but globally (without PARTITION) by `Id` now.

So this prompted a question of how does order of columns in SELECT affect sort order in the final result?
Given 3 columns, there are 6 column ordering combinations.

When ORDER without partition is placed last, the 1st two columns and be switched and give same result.
When PARTITION with ORDER is placed last, same as above.
When PARTITION without ORDER is placed last, there are different results.

1. When global (no partition) ORDER is 1st, the result is grouped by partitions and ordered within each partition

Why didn’t the 3rd column mess up the “within partition ordering”? If selected alone, it will not be ordered within each partition. It seems to have propagated the ordering of the 2nd column which was PARTITION with ORDER.

1. When global (no partition) ORDER is 2nd, the Id within each partition is not ordered (probably because the global ORDER messed up the 1st column’s results).

From these experiments, is it right to say SQL server evaluates columns in SELECT from left to right, and that the latest evaluated PARTITION + ORDER is the final order?

I don’t want to go on to multiple columns in PARTITION.