Select... From... Where... Group... Having... Order...

(Classic) an error occurs when using group by. What should I pay attention?

Select * from Products group by CategoryID
The database I use is NorthWind.

Error message:
Msg 8120, Level 16, State 1, Line 2
Column 'products. productid' is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

========================================================== ============
To put it simply.
When using group by, the same value in a field will be considered as one,
Table 1 is as follows:
ID name
1 xiaowang
2 xiaowang
3 xx
4 bb
Select name from table1 group by name
The result is:
Xiaowang
Xx
Bb
In other words, we can simply think that the same situation is only taken once.
Therefore, the obtained table has a discarded image with the original table, so select * cannot be used,
Theoretically, this can only be used with sum, count!
That is, the field to be read must also be able to "decrease" The line accordingly!

========================================================== ============
The principle of group by is that all columns after select do not use aggregate functions and must appear after group.
Therefore, all the column names you want to display should be written
After group

========================================================== ============
Select item. itemnum, item. in1, item. in4, inventory. location from item, inventory
Where item. itemnum = inventory. itemnum
And inventory. location = 'dyb'
And item. in1 = 'd/MTD/MRM'
Group by item. ITEMNUM
The error message is not a group by expression.
The tall man can tell me that I had an error there and how to use GROUP.
Thank you!

========================================================== ============

Group by is a GROUP query. Generally, group by is used with Aggregate functions.

You use group by to group by the ITEM. ITEMNUM field. If the content of other fields is different, how can this problem be displayed?

A B
1 abc
1 bcd
1 asdfg

Select A, B from table group by
What are the results,

A B
Abc
1 bcd
Asdfg

How do the three entries on the right become one? So we need to use aggregate functions, such

Select A, count (B) quantity from table group by
The result is
A quantity
1 3

The principle of group by is that all columns after select do not use aggregate functions and must appear after group.

========================================================== ============

Summary:

In the select statement, you can use the group by clause to divide rows into smaller groups. Then, use the group function to return the summary information of each group. In addition,You can use the having clause to limit the returned result set.. The group by clause can group query results and return the summary information of rows. Oracle groups query results by the value of the expression specified in the group by clause.

In a query statement with a group by clause, the columns specified in the select list are either columns specified in the group by clause or contain clustering functions.

Select max (sal), job emp group by job;
(Note max (sal): the job does not necessarily appear, but it makes sense)

The select and group by clauses of the query statements. The having clause is the only place where clustering functions appear. In the where clause, clustering functions cannot be used.

Select deptno, sum (sal) from emp where sal> 1200 group by deptno having sum (sal)> 8500 order by deptno;

When having clause is used in the gropu by clause, only groups meeting having conditions are returned in the query results. A SQL statement can contain the where clause and having clause. Having is similar to the where clause and is used to set conditions.

Roles of the where clauseBefore grouping query results, remove rows that do not meet the where condition, that is, filter data before grouping. The condition cannot contain a clustering function, use the where condition to display specific rows.
Function of having clauseIt is used to filter groups that meet the conditions, that is, to filter data after the group. The conditions often contain clustering functions. You can use the having condition to display specific groups. You can also use multiple grouping criteria to group data.

Queries the number of employees for each position in each department
Select deptno, job, count (*) from emp group by deptno, job;

The principle of group by is that all columns after select do not use aggregate functions, and must appear after group by. If there is an order by clause, the column after the clause after order by must also appear after group.
Select... from... where... group by... having... order...
Select deptno, sum (sal) from emp where sal> 1200 group by deptno having sum (sal)> 8500 order by deptno;

========================================================== ==========