Sunday 6 July 2014

Rules of GroupBy Clause


Group functions are the built-in SQL functions that act on groups of rows and return one value for the entire group.

Rules of GroupBy clause

The name may be a Column reference. Such a Column is called a grouping Column: its values will be grouped for the final result.

* The group by clause should contain all the columns in the select list expect those used along with the group functions.

Example:
Let us take the below query,
SELECT location, dept, SUM (salary) 
FROM employee 
GROUP BY location, dept;

In the above query, if the columns 'location' and 'dept' are not specified next to GroupBy clause, then it results an error.


* It is upto us to include aggregated columns next to GroupBy. Meaning, in the above mentioned example it is upto us to include column 'salary' next to the GroupBy clause.

* We cannot include a column next to GroupBy, if that particular column is not selected.

No comments:

Post a Comment

GROUPBY VS PARTITIONBY