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.
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
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).