Perform data statistics on different values ​​of the same field in SQL

Application scenario: It is necessary to count the number of seals in different states according to the different states of the seals.

When I first searched on Baidu, I did find different answers, but I can only blame myself for not understanding the SQL syntax well enough and failing to write it out, which led to the following incorrect writing.

select b.corporateOrgName, b.corporateOrgGuid companyId,
count(case when bc.ftype not in(1,2) then 1 else 0 end ) total,
count(case when bc.ftype in(3,4,5) then 1 else 0 end ) usetotal,
count(case when bc.ftype = 6 then 1 else 0 end ) saveTotal,
count(case when bc.ftype = 7 then 1 else 0 end ) returnTotal
from B_seal_cycle bc
join B_seal b
on bc.sealId = b.id
where b.corporateOrgName like '%%'
group by b.corporateOrgName,b.corporateOrgGuid

The logic makes sense, but I just can't get the ideal interface, so every piece of statistical data written is the same. Correct spelling after the change

select b.corporateOrgName, b.corporateOrgGuid companyId,
count(case when bc.ftype not in(1,2) then 1 end ) total,
count(case when bc.ftype in(3,4,5) then 1 end ) usetotal,
count(case when bc.ftype = 6 then 1 end ) saveTotal,
count(case when bc.ftype = 7 then 1 end ) returnTotal
from B_seal_cycle bc
join B_seal b
on bc.sealId = b.id
where b.corporateOrgName like '%%'
group by b.corporateOrgName,b.corporateOrgGuid

Do you see the difference? Removing the else 0 will give the correct result.

Problems encountered

1. The interpretation of case when syntax is incorrect.

After adding else, the result will always be 1 or 0.

2. The count function will count whether it is 1 or 0.

3. After adding else 0, statistics can be performed using the sum function.

You can also write

select b.corporateOrgName, b.corporateOrgGuid companyId,
sum(case when bc.ftype not in(1,2) then 1 else 0 end ) total,
sum(case when bc.ftype in(3,4,5) then 1 else 0 end ) usetotal,
sum(case when bc.ftype = 6 then 1 else 0 end ) saveTotal,
sum(case when bc.ftype = 7 then 1 else 0 end ) returnTotal
from B_seal_cycle bc
join B_seal b
on bc.sealId = b.id
where b.corporateOrgName like '%%'
group by b.corporateOrgName,b.corporateOrgGuid

If you have any questions or better ways of writing, please leave a message.

Additional knowledge: What is the difference between executing statements DESC and DESCRIBE in SQL language?

DESCRIBE TABLE is used to list all columns in the specified table or view.

DESCRIBE INDEX FOR TABLE is used to list all indexes of the specified table.

So DESCRIBE is used to display data structure information;

Desc stands for descendant, which is used to sort the results when querying, in descending order.

DESCRIBE is an abbreviation for SHOW COLUMNS FROM.

DESCRIBE provides information about the columns of a table. col_name can be a column name or a string containing the SQL wildcard characters "%" and "_". There is no need to surround the string with quotes.

1. The describe command is used to view detailed design information of a specific table

For example, to view the design information of the guestbook table, you can use:

describe guestbook describe ol_user userid

2. You can view the column names of the tables in the database through "show comnus"

There are two ways to use it:

show columns form table name from database name

or:

show columns from database name.table name

3. Use the describe command to query specific column information

describe guestbook id is to query the column information of the id field in guestbook

{DESCRIBE | 
DESC
} tbl_name [col_name | wild]

DESCRIBE is an abbreviation for SHOW COLUMNS FROM.

DESCRIBE provides information about the columns of a table. col_name can be a column name or a string containing the SQL wildcard characters "%" and "_". There is no need to surround the string with quotes.

mysql> 
desc
ol_user username\G

4. Determine whether a field exists

mysql_connect(
'localhost'  
,   
'root'  
,   
'root'   
);
   
mysql_select_db(   
'demo'   
); 
$test = mysql_query(
'Describe cdb_posts first'
); 
$test = mysql_fetch_array($test);

$test[0] returns the name of the field. For example, if I want to query the first field, the returned value is first.

If this field does not exist, NULL is returned. This is how you can determine whether a field exists.

The above article on performing data statistics operations on different values ​​of the same field in SQL is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.