How to display in a query with a GROUP BY clause
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.
