Query Behavior

The difference between GROUP BY and OVER() clause is that GROUP BY collapses a group of rows into one row, making result with potentially less rows than without GROUP BY. OVER() clause doe not change the number of rows. It just adds a column to your result set. OVER() clause is calculated just before ORDER BY operation, after all the joins, WHERE, GROUP BY, HAVING etc. are finished.

First query does not have GROUP BY, therefore it will return the same number of rows as @temp has - 6 rows. It will display SEQ column, and next to it there will be TERR_CNT value which is calculated like this:

Find all the rows that have the same TERR_CD as the current row, and in that set of rows COUNT those rows which have SEQ NOT NULL.

For the first row TERR_CD is 0203. No other rows has that TERR_CD, so COUNT() OVER() is 1.

For the second row TERR_CD is 0561. 3 rows has that TERR_CD, so COUNT() OVER() is 3.

For the third row TERR_CD is 0561. 3 rows has that TERR_CD, so COUNT() OVER() is 3.

Etc...

Second query first filters to only one row with a WHERE clause. After that, OVER() is calculated exactly the same as described before.

The COUNT is one.