Is it possible to use Aggregate function in a Select statment without using Group By clause?

Question

So far I have written Aggregate function followed by Group By clause to find the values based on SUM, AVG and other Aggregate functions. I have a bit confusion in the Group By clause. When we use Aggregate functions what are the columns I need to specify in the Group By clause. Otherwise Is there any way to use Aggregate functions without using Group By clause.

Answer 1

All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY

Good:

SELECT col1, col2, col3, MAX(col4) ... GROUP BY col1, col2, col3 

Also good:

SELECT col1, col2, col3, MAX(col4) ... GROUP BY col1, col2, col3, col5, col6 

No other columns = no GROUP BY needed

SELECT MAX(col4) ... 

Won't work:

SELECT col1, col2, col3, MAX(col4) ... GROUP BY col1, col2 

Pointless:

SELECT col1, col2, col3, MAX(col4) ... GROUP BY col1, col2, col3, MAX(col4) 

Having an aggregate (MAX etc) with other columns without a GROUP BY makes no sense because the query becomes ambiguous.

Answer 2

You can use Select AGG() OVER() in TSQL

SELECT *, SUM(Value) OVER() FROM Table 

There are other options for Over such as Partition By if you want to group:

SELECT *, SUM(Value) OVER(PARTITION By ParentId) FROM Table 

http://msdn.microsoft.com/en-us/library/ms189461.aspx