[SQL] how to display the result with Count () 0 in select statements containing group

1. Problem

In the following exampletable table, find the number of records with each type (categoryid) meeting the flag value of 1.

ID	Flag	Categoryid
1	1	1
2	1	1
3	1	2
4	1	2
5	0	3

Table 1-exampletable

The ideal result is as follows:

categoryid	totalnum
1	2
2	2
3	0

Table 2-ideal output results

At first glance, we can easily write the following statement:

SelectCategoryid,Count(1)AsTotalnumFromExampletableWhereFlag= 1 Group ByCategoryid
 

The following result is displayed after running:

categoryid	totalnum
1	2
2	2

Table 3-error output results

No records with categoryid = 3 and totalnum = 0.

2. Cause Analysis:

The reason for the above results is that the WHERE clause in the SELECT statement is executed before the Group by clause. Therefore, when the group by clause is executed

The records in the table have filtered out the records whose category is 3, which are not included in group processing.

Supplement: complete execution sequence of SQL select statements

 
The complete execution sequence of the SQL SELECT statement:

1. The from clause assembles data from different data sources;

2. The WHERE clause filters records based on specified conditions;

3. The group by clause divides data into multiple groups;

4. Use Aggregate functions for computation;

5. Use the having clause to filter groups;

6. Calculate all expressions;

7. Use order by to sort the results.
 

3. solution:

Construct a connection between the result set containing all categoeyids and the result set obtained from the preceding statement, and use null to replace the function.

(For example, isnull () in SQL Server and nvl () in Oracle) replace null with 0.

The example statement is as follows:

Select  Distinct  Maintable. categoryid,  Isnull  (Subtable. subnum,  0  )  As  Totalnum
  From  Exampletable  As  Maintable
  Left  Join  
(
 Select  Categoryid,  Count  (  1  )  As  Subnum  From  Exampletable  Where  Flag  =  1  Group  By Categoryid
)  As  Subtable
  On  Maintable. categoryid  =  Subtable. categoryid 

After execution, the correct result is returned:

categoryid	totalnum
1	2
2	2
3	0

Table 4-correct output results

I read everyone'sReply,We can come up with a better and more general method. Thank you for the following:
Sum Method:
If the flag value is only 0 and 1, a statement is used directly.

SelectCategoryid,Sum(FLAG)AsTotalnumFromTbl_category_testGroup ByCategoryid
 

If there are other flag values, you can use case when to construct a subquery:

 Select Categoryid, Sum (Isright) As Totalnum From  (  Select Categoryid, Case   When Flag =   1   Then   1   Else   0   End   As  Isright  From  Exampletable)  As  Temptable  Group   By Categoryid

Well, we still need to talk more about learning, and we can always come up with a better solution.