Is it possible to use Aggregate function in a Select statment without using Group By clause?
Solution 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.
Solution 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
Solution 3
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).
Solution 4
The Columns which are not present in the Aggregate function should come on group by clause:
Select
Min(col1),
Avg(col2),
sum(col3)
from table
then we do not required group by clause, But if there is some column which are not present in the Aggregate function then you must use group by for that column.
Select
col1,
col2,
sum(col3)
from table
group by col1,col2
then we have to use the group by for the column col1 and col2
Solution 5
You must group by columns that do not have aggregate functions on them.
You may avoid a group by clause if all columns selected have aggregate functions applied.
Related videos on Youtube
thevan
Software Engineering Senior Analyst at Accenture Solutions Private Limited, Chennai, India. Interested in ASP.Net, MVC, Web API, WCF, Web Services, ADO.Net, C#.Net, VB.Net, Entity Framework, MS SQLServer, Angular.js, JavaScript, JQuery, Ajax, HTML and CSS
Updated on July 09, 2022Comments
-
thevan almost 2 years
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.
-
thevan almost 13 yearsSo In the Group By Clause, we need to specify all the Columns what we have selected and we may of may not include the aggregate Column. This is what you have specified. Am I right?
-
ZygD almost 13 yearsCorrect, but you shouldn't include the aggregate column. That also makes no sense
-
ZygD almost 13 years+1 Note for GROUP BY will collapse rows to the grouping, PARTITION BY will keep all rows. Still useful though
-
sventechie almost 8 yearsSometimes it is possible to just group by the row ID for a given table -- discovered with trial and error in PostgreSQL 9.5.
-
ZygD almost 8 years@sventechie: PG honours the SQL standard more than others, which says (I think!) that
GROUP BY <primary key>
should be the same asGROUP BY <primary key>, <nonkey column1>, <nonkey column2>
. That is, the PK is unique already so nonkey columns can be ignored -
JoeBass over 7 yearsFeels super hacky, but it worked for my case. Also seemed to not be overly optimized, as my query slowed down significantly.
-
WestCoastProjects over 5 yearsThis is the best answer for the spirit of the question.
-
bonnal-enzo about 4 yearsNote: we call this a window function