SQL GROUP BY Tutorial

SQL GROUP BY clause is used to perform aggregation in any data warehousing environment. To do aggregate in SQL we use GROUP BY clause. All the columns in SELECT clause on which any aggregation is not done must be part of GROUP BY clause in SQL. The GROUP BY clause group the records on the basis of axis columns and then perform the aggregation on other columns. The syntax of GROUP BY clause is as below:

SELECT column1, column2, sum(column3), count(column4)
FROM table
GROUP BY column1,column2;

In the above example, all the records with same value for combination of column1 and column2 will be clubbed together and then sum and count on column3 and column4 will be done respectively. So in the final output, we will get only one row for combination of column1 and column2. In this manner, you can calculate the aggregate in SQL. There are various aggregate functions in SQL available for users:

SQL COUNT Function: SQL COUNT Function returns the total number of rows satisfying the condition. As the name suggests, if we need to get the COUNT or TOTAL ROWS , then we use this SQL AGGREGATE FUNCTION. Example of SQL COUNT FUNCTION:

SELECT CUST_CITY,COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY WHERE CUST_CITY=’DELHI’;

RETURNS TOTAL NUMBER OF ROWS IN THE TABLE WHERE CUST_CTY IS DELHI

SELECT CUST_CITY,COUNT(*) FROM CUSTOMERS GROUP BY CUST_CITY;

SQL AGGREGATE FUNCTION-COUNT
SQL AGGREGATE FUNCTION-COUNT

RETURNS TOTAL NUMBER OF ROWS IN THE TABLE.

SQL MAX Function: SQL MAX Function returns the maximum value of the column for all the rows satisfying the condition. As the name suggests, if we need to get the MAX value for any column , then we use this SQL AGGREGATE FUNCTION. Example of SQL MAX FUNCTION:

SELECT MAX(CUST_ID) FROM CUSTOMERS WHERE CUST_CITY=’DELHI’;

RETURNS MAX VALUE OF CUST_ID IN THE TABLE WHERE CUST_CTY IS DELHI

SELECT MAX(CUST_ID) FROM CUSTOMERS ;

RETURNS MAX VALUE OF CUST_ID IN THE TABLE.

SQL MIN Function: SQL MIN Function returns the minimum value of the column for all the rows satisfying the condition. As the name suggests, if we need to get the MIN value for any column , then we use this SQL AGGREGATE FUNCTION. Example of SQL MIN FUNCTION:

SELECT MIN(CUST_ID) FROM CUSTOMERS WHERE CUST_CITY=’DELHI’;

RETURNS MIN VALUE OF CUST_ID IN THE TABLE WHERE CUST_CTY IS DELHI

SELECT MIN(CUST_ID) FROM CUSTOMERS ;

RETURNS MIN VALUE OF CUST_ID IN THE TABLE

SQL AVG Function: SQL AVG Function returns the average value of the column for all the rows satisfying the condition. As the name suggests, if we need to get the AVG value for any column , then we use this SQL AGGREGATE FUNCTION. Example of SQL AVG FUNCTION:

SELECT AVG(CUST_ID) FROM CUSTOMERS WHERE CUST_CITY=’DELHI’;

RETURNS AVG VALUE OF CUST_ID IN THE TABLE WHERE CUST_CTY IS DELHI

SELECT AVG(CUST_ID) FROM CUSTOMERS ;

RETURNS AVG VALUE OF CUST_ID IN THE TABLE

SQL SUM Function: SQL SUM Function returns the sum value of the column for all the rows satisfying the condition. As the name suggests, if we need to get the SUM for any column , then we use this SQL AGGREGATE FUNCTION. Example of SQL SUM FUNCTION:

SELECT SUM(CUST_ID) FROM CUSTOMERS WHERE CUST_CITY=’DELHI’;

RETURNS SUM VALUE OF CUST_ID IN THE TABLE WHERE CUST_CTY IS DELHI

SELECT SUM(CUST_ID) FROM CUSTOMERS ;

RETURNS SUM OF CUST_ID IN THE TABLE

SQL Aggregate Functions are very useful and are extensively used in any Data Warehouse environment. So it is required to understand the aggregation properly in SQL.

Leave a Reply

Your email address will not be published. Required fields are marked *