Shulou(Shulou.com)05/31 Report--

How to display queries containing GROUP BY clause, for this problem, this article details the corresponding analysis and solution, hoping to help more small partners who want to solve this problem find a simpler and easier way.

In SQL Server database query, in order to compare and analyze query results, we often use GROUP BY clause and COUNT() function to classify and count query results. But we often have some problems in the process of using. The following is an example of how a query with a GROUPBY clause can be displayed:

How to display in queries with GROUPBY clauses

1. Question:

In the following ExampleTable table, find the number of records in each category (CategoryID) that satisfy Flag = 1.

IDFlagCategoryID

111

211

312

412

503

The ideal result should be as follows:

CategoryIDTotalNum

12

22

30

At first glance, it is easy to write the following sentence:

SELECTCategoryID,COUNT(1)ASTotalNumFROMExampleTableWHEREflag=1GROUPBYCategoryID

The runnable results are as follows:

CategoryIDTotalNum

12

22

No records with CategoryID=3, TotalNum=0.

How to display in queries with GROUPBY clauses

2. Cause analysis:

The reason for the above result is that the WHERE clause is executed before GROUPBY in the SELECT statement, so when the GROUPBY clause is executed, the records in the table have already filtered the records with Category=3, and naturally will not be counted in the grouping process.

Complete execution order of SQL SELECT statements:

From clause assembles data from different data sources;

The WHERE clause filters records based on specified conditions.

The GROUP BY clause divides the data into groups.

4. Use aggregation function to calculate;

5. Use HAVING clause to filter grouping;

6. Compute all expressions;

Use ORDERBY to sort results.

3. Solution:

Construct a result set containing all CategoeyIDs and join the result set obtained by the statements above it, and replace NULL with 0 by using NULL replacement functions (such as ISNULL() in SQLSERVER and NVL() in ORACLE).

An example statement is as follows: SELECTDISTINCTMainTable.CategoryID,ISNULL(SubTable.SubNum,0)ASTotalNumFROMExampleTableASMainTableLEFTJOIN(SELECTCategoryID,COUNT(1)ASSubNumFROMExampleTableWHEREflag=1GROUPBYCategoryID)ASSubTableONMainTable.CategoryID=SubTable.CategoryID

When executed, it returns the correct result:

CategoryIDTotalNum

12

22

30

About how to display the answer to the question in the query containing the GROUP BY clause is shared here. I hope the above content can be of some help to everyone. If you still have a lot of doubts, you can pay attention to the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.