How can we connect two identical SQL statements using the multi-table join method?
For example, in the users table, the state field indicates the user State, 1 indicates available, and 0 indicates unavailable. How can I display the number of available and unavailable users in a table?
The table also contains the userid and username fields.
1. UseSum+Case
Declare @ T Table
(Userid Int , Username Varchar ( 8 ), State Int )
Insert Into @ T
Select 1 , ' Zhangsan ' , 0 Union All
Select 2 , ' Lisi ' , 1 Union All
Select 3 , ' Wangwu ' , 1 Union All
Select 4 , ' Liuliu ' , 0 Union All
Select 5 , ' Chenqi ' , 0 Union All
Select 6 , ' WUBA ' , 0
Declare @ Available users Int , @ Number of unavailable users Int
Select
@ Available users = Sum ( Case State When 0 Then 1 Else 0 End ),
@ Number of unavailable users = Sum ( Case State When 1 Then 1 Else 0 End )
From @ T
Select * , @ Available users As Available users, @ Number of unavailable users As Number of unavailable persons
From @ T
/*
Userid username State number of available persons not available
----------------------------------------------------
1 zhangsan 0 4 2
2 Lisi 1 4 2
3 wangwu 1 4 2
4 liuliu 0 4 2
5 chenqi 0 4 2
6 WUBA 0 4 2
*/
2. UseCount+Case
Select Count ( Case When State = 1 Then 1 Else 0 End ) As ' Available ' ,
Count ( Case When State = 0 Then 1 Else 0 End ) As ' Unavailable ' ,
Count ( 1 ) As ' Total number '
From Users