SQL Group Statistics 10, SQL Group Statistics

select [salesman],sum([sales]) all* ,product category from [sales details] a inner join (select [salesman],sum(sales) all from [sales details] inner join ()group by [salesman]) b on a.[Salesman]=b.[Salesman] group by [Salesman], product category.

Personal advice.

select sno,type ,count(type)from table group by sno,type

This is vertical, horizontal below.

select sno,sum(case when type='a' then 1 else 0 end) as t_a,sum(case when type='b' then 1 else 0 end) as t_b,sum(case when type='c' then 1 else 0 end) as t_c from table group by sno

Grouping makes sense when the array is repeated, because a person can also be divided into a group, but it doesn't make sense, and the grouping is done with a group by statement, and the syntax is as follows:

Example: Group by department number, find the number of people in each department, the average salary.

Group by position to find the maximum and minimum wages for each position:

Once grouped, there are actually new grammatical limitations:

The grouping function can be used alone when there is no grouping, but no other query fields can appear

ename is the other query field. After the select clause, only grouped fields and statistical functions can appear, and no other fields can appear.

Grouping functions are allowed to be nested, but no other fields can appear in the query of the nested grouping function.

Example: Calculate the salary with the highest average salary grouped by position.

When adding another field 'job' I get an error.

Example: Find out the name, location, number of people in each department, and average salary.

Determine the required datasheet:

EMP table: number of people in the department, average salary.

dpt table: the name and location of the department.

Identify known associated fields:

According to the previous understanding of grouping, as long as the data is duplicated, it is possible to query the grouping, but at this time, it is not the same as the previous grouping operation, the previous grouping is grouped for an entity table (emp, dept belong to the entity table), but for the above data is displayed through the query results, so it is a temporary virtual table, but regardless of whether it is an entity table or a virtual table, as long as there is a duplicate, Then let's go straight to the grouping.

You need to use group

by statement.

1. Create a test table and insert data

create table test

id int,name varchar(10),score int,classname varchar(20));

insert into test values (1,'Zhang San',100,'One shift');

insert into test values (2,'Li Si',89,'One shift');

insert into test values (3,'Wang V',97,'One shift');

insert into test values (4,'Zhao Liu',87,'Second shift');

insert into test values (5,'Sun Qi',94,'Second shift');

insert into test values (6,'Yang Ba',76,'Second shift');

2. To query the total score of each class, you can use the following statement:

select classname,sum(score) as total score from test group by classname;

3. Screenshot of the result: