Statistical query-two ways to implement count based on criteria-oracle__oracle

SQL statement:

Select B.departmentname, B.name, 
       count (case if b.businessstate= ' Notsubmit ' then 1  else null end) notsubmit,< C2/>count (case when b.businessstate= ' abandoned ' then 7  else null-end) abandoned,
        count (b.id) allcounts
From Business B GROUP by B.departmentname, B.name

To qualify the condition, count is counted by using the word when then else.

In addition, you can build a view and count by sum:

View:

Create or replace view View_business_statistics
as select b.ID, b.name as businessname,b.departmentname,b.sldate as S Ldate, b.applydate as Applydate,
         decode (b.businessstate, ' Notsubmit ', 1,0) as Notsubmitnum,
         decode ( B.businessstate, ' approving ', 1,0) as Approvingnum, from
business B;

SQL statements that are counted by sum:

Select B.departmentname, B.name, 
       sum (b.notsubmitnum) notsubmit, 
       sum (b.abandoned) abandoned,
       count (b.id ) allcounts
from Business B group by B.departmentname, B.name

Create a view use Oracle's own decode keyword (equivalent to case when), repeat the columns 0 and 1, and use sum to get the total count.

The idea of building a view is to increase flexibility.