29

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
  • 10,052
  • 53
  • 137
  • 202

7 Answers7

45

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • So 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? – thevan Jun 24 '11 at 11:48
  • Correct, but you shouldn't include the aggregate column. That also makes no sense – gbn Jun 24 '11 at 11:50
  • Sometimes it is possible to just group by the row ID for a given table -- discovered with trial and error in PostgreSQL 9.5. – sventechie Jun 21 '16 at 20:16
  • 1
    @sventechie: PG honours the SQL standard more than others, which says (I think!) that `GROUP BY ` should be the same as `GROUP BY , , `. That is, the PK is unique already so nonkey columns can be ignored – gbn Jun 22 '16 at 12:00
33

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

anthony sottile
  • 61,815
  • 15
  • 148
  • 207
9

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

nvogel
  • 24,981
  • 1
  • 44
  • 82
3

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

Nadjib Mami
  • 5,736
  • 9
  • 37
  • 49
Bhawesh Deepak
  • 51
  • 3
3

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

You omit columns from the SELECT inside aggregate functions, all other columns should exist in GROUP BY clause seperated by comma.

You can have query with aggregates and no group by, as long as you have ONLY aggregate values in the SELECT statement

niktrs
  • 9,858
  • 1
  • 30
  • 30
0

Yes, without aggregate functions we can use group by column but it will be a distinct function.

select <column name> from <table name> group by <column name>

Given the table EMP: 

select * from EMP

That outputs:

7369    SMITH   CLERK       7902
7499    ALLEN   SALESMAN    7698
7521    WARD    SALESMAN    7698
7566    JONES   MANAGER     7839

Adding a group by

select job from EMP group by job

Outputs:

CLERK
SALESMAN
MANAGER
β.εηοιτ.βε
  • 33,893
  • 13
  • 69
  • 83
Chandu Reddy
  • 1
  • 1