SQL GROUP by usage and GROUP by syntax

Source: Internet
Author: User

SQL and GROUP by are used to group data, and we look at the database table called Employeehours to store the daily time of each company's employees:

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 company manager wants to get all of the employees ' working hours superimposed, he needs to execute the following SQL statements:

SELECT SUM (Hours)
From Employeehours

But what if the manager wants to get the sum of his employees for every time?
To do this, he needs to modify his SQL query and the GROUP by statement using sql:

SELECT Employee, SUM (Hours)
From Employeehours
GROUP by Employee

The result of the SQL expression above would be the following:

Employee Hours
John Smith 25
Allan Babel 24
Tina Crown 27

The SQL's GROUP by clause can be used for other SQL aggregate functions, such as SQL avg:

SELECT Employee, AVG (Hours)
From Employeehours
GROUP by Employee

Employee Hours
John Smith 8.33
Allan Babel 8
Tina Crown 9

In the date column of our employee table, we can also group

SELECT Date, SUM (Hours)
From Employeehours
GROUP by Date

This is the result of the above SQL expression:

Date Hours
5/6/2004 24
5/7/2004 27
5/8/2004 25

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.