|
MAKE FULL WEB APPLICATION WITH JUST SQL KNOWLEDGE? CLICK HERE
To generate a summary output you need aggregate
functions along with the GROUP BY clause in a SELECT statement.
GROUP BY and HAVING clause are parallel to WHERE
and ORDER BY clauses except that they act on groups rather than
or individual rows if the objective is to find out the maximum hours
of each employee . than the query will be :-
The syntax for the GROUP BY function is:-
| SELECT column,SUM(column) FROM table GROUP
BY column |
The best way to explain how and when to use the SQL GROUP BY
statement is by example,
Consider the following database table called Employee Hours
storing the daily hours for each employee of a company:-
|
Employee
|
Date
|
Hours
|
| Deepesh Agarwal |
5/6/2006
|
8
|
| Ramesh Verma |
5/6/2006
|
8
|
| Bharat Shah |
5/6/2006
|
8
|
| Deepesh Agarwal |
5/7/2006
|
9
|
| Ramesh Verma |
5/7/2006
|
8
|
| Bharat Shah |
5/7/2006
|
10
|
| Deepesh Agarwal |
5/8/2006
|
8
|
| Ramesh Verma |
5/8/2006
|
8
|
| Bharat Shah |
5/8/2006
|
9
|
If the manager of the company wants to get the simple sum of all
hours worked by all employees, he needs to execute the following
SQL statement:-
| SELECT SUM (Hours) FROM Employee Hours |
But what if the manager wants to get the sum of all hours for each
of his employees?To do that he need to modify his SQL query and
use the SQL GROUP BY statement:
|
SELECT Employee, SUM (Hours) FROM
Employee Hours GROUP BY Employee
|
The result of the SQL expression above will be the following:-
|
Employee
|
Hours
|
| Deepesh Agarwal |
25
|
| Ramesh Verma |
24
|
| Bharat Shah |
27
|
As you can see we have only one entry for each employee, because
we are grouping by the Employee column.
The SQL GROUP BY clause can be used with other SQL aggregate
functions, for example SQL AVG:-
|
SELECT Employee, AVG(Hours) FROM
Employee Hours GROUP BY Employee
|
The result of the SQL statement above will be :-
|
Employee
|
Hours
|
| Deepesh Agarwal |
8.33
|
| Ramesh Verma |
8
|
| Bharat Shah |
9
|
In our Employee table we can group by the date column too, to find
out what is the total number of hours worked on each of the dates
into the table:
| SELECT Date, SUM(Hours) FROM Employee
Hours GROUP BY Date |
Here is the result of the above SQL expression :-
|
Date
|
Hours
|
| 5/6/2006 |
24 |
| 5/7/2006 |
27 |
| 5/8/2006 |
25 |
If you don't find what you are looking for. Please click
here to submit your query, our experts will reply soon.
|
