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

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.

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

Yes you can use an aggregate without GROUP BY:

SELECT SUM(col) FROM tbl;

This will return one row only - the sum of the column "col" for all rows in tbl (excluding nulls).

Related