The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s).
The best way to explain how and when to use the SQL GROUP BY statement is by example, and that’s what we are going to do.
Consider the following database table called EmployeeHours storing the daily hours for each employee of a factious company:
Employee Date Hours
John Smith 5/6/2004 8
Allan Babel 5/6/2004 8
Tina Crown 5/6/2004 8
John Smith 5/7/2004 9
Allan Babel 5/7/2004 8
Tina Crown 5/7/2004 10
John Smith 5/8/2004 8
Allan Babel 5/8/2004 8
Tina Crown 5/8/2004 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 EmployeeHours
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 EmployeeHours
GROUP BY Employee
The result of the SQL expression above will be the following:
Employee Hours
John Smith 25
Allan Babel 24
Tina Crown 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 EmployeeHours
GROUP BY Employee
The result of the SQL statement above will be:
Employee Hours
John Smith 8.33
Allan Babel 8
Tina Crown 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 EmployeeHours
GROUP BY Date
Here is the result of the above SQL expression:
Date Hours
5/6/2004 24
5/7/2004 27
5/8/2004 25
Popular Posts
-
# When the user submits form, his information is sent to the corresponding servlet file because we've set the ACTION attribute to point ...
-
Syntax of JSP Scriptles are: <% //java codes %> JSP Scriptlets begins with <% and ends %> .We can embed any a...
-
JSP scripting elements let you insert Java code into the servlet that will be generated from the current JSP page. There are three forms: ...
-
JSP pages are high level extension of servlet and it enable the developers to embed java code in html pages. JSP files are finally compiled ...
-
To simplify code in JSP expressions and scriptlets, you are supplied with eight automatically defined variables, sometimes called implicit o...
-
<%@page contentType="text/html" import="java.util.*" %> <!-- http://www.roseindia.net/jsp --> ...
-
Syntax of JSP Declaratives are: <%! //java codes %> JSP Declaratives begins with with .We can embed any amount of java c...
-
Accessing the Standard CGI Variables: To build the successful web application, you often need to know a lot about the environment in which ...
-
*When the HTTP client (e.g. a browser) sends a request, it is required to supply a request line (usually GET or POST). * If it wants to...
-
In this lesson we will learn about the various tags available in JSP with suitable examples. In JSP tags can be devided into 4 different typ...
No comments:
Post a Comment