SQL statement GROUP BY usage, sqlgroup

SQL statement GROUP BY usage, sqlgroup

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.

How to Use group by in SQL statements

1. The most common syntax is Select CategoryID, AVG (UnitPrice), COUNT (UnitPrice) FROM dbo. products Where UnitPrice> 30 group by CategoryID order by CategoryID DESC: the average unit price and unit price of all Products are queried. And the unit price is more than 30 records. 2. Let's look at this syntax: Select CategoryID, AVG (DISTINCT UnitPrice), COUNT (DISTINCT UnitPrice) FROM dbo. products Where UnitPrice> 30 group by CategoryID order by CategoryID DESC when DISTINCT is used, the average unit price of repeated prices is removed. 3. If you want to use conditional filtering after classification statistics, the following statement can be used AS the parameter: Select CategoryID, SUM (UnitPrice) AS SumPriceFROM dbo. productsGROUP BY CategoryIDHAVING SUM (UnitPrice)> 300 HAVING is similar to the Where statement. Where is used to filter data before classification, while HAVING is used to filter data after classification. It uses AND, OR, NOT, and like in the same way as Where. 4. If you want to add a summary row in classification statistics, you can use the following statement: Select CategoryID, SUM (UnitPrice), GROUPING (CategoryID) AS 'grouping' FROM dbo. the ProductsGROUP BY CategoryID WITH ROLLUPGrouping column is used to identify which row is a summary row. It uses the ROLLUP operation to add a summary row. 5. If you use with cube, a multi-dimensional classification dataset is generated, AS follows: Select CategoryID, SupplierID, SUM (UnitPrice) AS SumPriceFROM dbo. productsGROUP BY CategoryID, SupplierID with cube it will generate a cross table and generate a summary of all possible combinations. 6. Using rollup cube produces a NULL value. You can use the following syntax to solve this problem: Select case when (GROUPING (SupplierID) = 1) THEN '-1' ELSE SupplierID end as SupplierID, SUM (UnitPrice) AS QtySumFROM dbo. productsGROUP BY SupplierID WITH CUBE

How to Use group by in SQL statements

Group by is an aggregate function that aggregates queried columns. For example:

C1 C2 C3
A 1 99
A 2 83
A 3 99
B 3 22
SELECT C1, SUM (C2) from t group by C1;