Overview
This article gives knowledge on two main things, sorting data into groups and returning the data for that group as a whole, and aggregation functions available in SQL. Aggregation is another way of saying a summary of data. For example, aggregating data might involve finding the average age of film club members or counting how many members live in a particular state.
Grouping
GROUP BY clause that is used in conjunction with the SELECT statement. It allows you to group identical data into one subset rather than listing each record. The GROUP BY clause is at its most powerful when used with SQLs summarizing and aggregating functions. The GROUP BY clause must go after any FROM or WHERE clauses in the SELECT statement. All the columns you want to be grouped must be listed in the GROUP BY column list. When dealing with a GROUP BY clause, the database system first creates a temporary results set based on the FROM and WHERE clauses. The DBMS then uses these results and looks for groups of identical records based on the column or columns specified in the GROUP BY clause. If your query includes a WHERE clause, like the one shown below, the grouping is based on the results returned by the WHERE clause:
SELECT DEPTNO
FROM EMP
WHERE DEPTNO IN (10,20,30)
GROUP BY DEPTNO;
The GROUP BY clause isn’t limited to just one column; it can be two or more as the following query shows:
SELECT DEPTNO, STATE
FROM EMP
WHERE DEPTNO IN (10,20,30)
GROUP BY DEPTNO, STATE;
The order of the columns in the GROUP BY clause affects the order of results, but it doesn’t change the results as a whole, just the order in which they arrive.
Group by Functions
There are several group functions like COUNT, MIN, MAX, AVG, SUM. Next sections we will discuss each of these.
COUNT
You can use the COUNT() function to count the number of records in the results. It’s used in the SELECT statement along with the column list. Inside the brackets, insert the name of the column you want counted. The value returned in the results set is the number of non-NULL values in that column. Alternatively, you can insert an asterisk (*), in which case all columns for all records in the results set are counted regardless of whether the value is NULL or not. The COUNT() function can also accept expressions.
SELECT COUNT (EMPID), COUNT (DEPTNO)
FROM EMP;
Normally all the group functions avoid NULL values in the column. If at all we have requirement where we need to see how many null values in a specific column then you need to use GROUP BY function.
SELECT EMPID, COUNT(Last_Name)
FROM EMP GROUP BY EMPID;
A GROUP BY clause essentially splits off results into groups, each being a subset of the full results. The COUNT() function in this case counts the results from each subset of the main results.
Keep watching more on this subject line.
|
| Author: Mahesh Raj 07 Jun 2008 | Member Level: Gold Points : 1 |
This is very good information,Continue posting such useful articles.
|