Number of times SQL queries repeat values

Source: Internet
Author: User
Number of times SQL queries repeat a table mark

Id name pay
1 Zhang San 1000
2 Wang Lei 1200
3 Zhang San 5853
4. Wang Wu 8574
5, high, 1259
6 High, 985

==============================
How to query the duplicate value of the name field? I don't know how to write the SQL statement. The result is that Michael Jacob appears several times, Wang Lei appears several times, and Gao appears several times.


In addition, I am not very familiar with the usage and meaning of group by. can anyone tell me what it means? thank you.


Reply to discussion (solution)

Select name, count (*) from mark grounp by name

Group by group key
Is based on who grouping

The group by syntax performs grouping statistics on the query results based on each member of the given data column to obtain a grouping summary table.
The column name in the SELECT clause must be a group column or column function. The column function returns a result for each GROUP defined BY the group by clause.
The structure and data of an employee information table are as follows:
Id name dept salary edlevel hiredate
1 Zhang San Development Department 2000 3
2 Li Si Development Department 2500 3 2009-10-01
3 Wang Wu design department 2600 5 2010-10-02
4 Wang 6 design department 2300 4
Ma Qi design department 2100 4 2010-10-06
6 Zhao BA sales department 3000 5
7 Qian Jiu Sales Department 3100 7
8 Sun ten sales department 3500 7
For example, to list the results of the highest salary for each department, the SQL statement is as follows:
Select dept, MAX (SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
The query result is as follows:
DEPT MAXIMUM
Development Department 2500
Design Department 2600
Sales 3500
Explain the result:
1. "the column name in the SELECT clause must be a GROUP column or a column function", because SELECT has the column DEPT contained in group by dept.
2. "the column function returns a result for each GROUP defined BY the group by Clause". according to the department grouping, a result is returned for each department, which is the highest salary for each department.
Note: Each department is calculated (the GROUP defined BY the group by clause) rather than the MAX (SALARY) of the entire company ).

Positive solution on the second floor. But the word is wrong.

Select name, COUNT (*) FROM mark group by name;

I remember a previous interview question.




The group by syntax performs grouping statistics on the query results based on each member of the given data column to obtain a grouping summary table.
The column name in the SELECT clause must be a group column or column function. The column function returns a result for each GROUP defined BY the group by clause.
The structure and data of an employee information table are as follows:
Id name dept salary edlevel hiredate
1 Zhang San Development Department 2000 3
2 Li Si Development Department 2500 3 2009-10-01
3 Wang Wu design department 2600 5 2010-10-02
4 Wang 6 design department 2300 4
Ma Qi design department 2100 4 2010-10-06
6 Zhao BA sales department 3000 5
7 Qian Jiu Sales Department 3100 7
8 Sun ten sales department 3500 7
For example, to list the results of the highest salary for each department, the SQL statement is as follows:
Select dept, MAX (SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
The query result is as follows:
DEPT MAXIMUM
Development Department 2500
Design Department 2600
Sales 3500
Explain the result:
1. "the column name in the SELECT clause must be a GROUP column or a column function", because SELECT has the column DEPT contained in group by dept.
2. "the column function returns a result for each GROUP defined BY the group by Clause". according to the department grouping, a result is returned for each department, which is the highest salary for each department.
Note: Each department is calculated (the GROUP defined BY the group by clause) rather than the MAX (SALARY) of the entire company ).



That is to say, if you want to query the sum or minimum number of a field, it is generally necessary to use group by to re-combine the data query.

I have tried it. for example, if you want to query select id, sum (dis) from mark, an error will be reported,
Select id, max (dis) from mark. This will also report an error.

Select id, max (dis) as name from mark this will also report an error

That is to say, if the field used for statistic query must use group by to separate a field in the statistic query, can this be understood?

Select id, max (dis) from mark group by dis is used to query the value queried by max (dis) and use group by to rename a column.

Sum and max are mathematical functions, and your dis column is of the balanced type. of course, an error is reported.
Group by means of grouping
Group by the content of the specified column with group
If no group by exists, all records are counted as one group.

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.