Oracle_ Multi-field statistics (multiple count)

Oracle_ Multi-field statistics

To query the synthesis of different values of the same field in the same table, the method is as follows:

Select O.code Gift Code,
O.name Gift Name,
L.couponactivityid coupon Activity Definition,
Count (L.couponno) as coupons total quantity,
Count (case
When l.state in (' 0 ') then
' 0 '
End) Number of vouchers not redeemed,
Count (case
When l.state in (' 1 ') then
' 1 '
End) has been redeemed for the number of non-write-off coupons,
Count (case
When l.state in (' 2 ') then
' 2 '
End) Number of certificates cancelled
From Css_coupon l, Css_award o
where L.couponactivityid = O.couponcode
and o.state = ' normal '
Group by O.code, O.name, L.couponactivityid

Usage Analysis:

Count (case
When l.state in (' 0 ') then
' 0 '
End) Number of vouchers not redeemed,
Count (case
When l.state in (' 1 ') then
' 1 '
End) has been redeemed for the number of non-write-off coupons,
Count (case
When l.state in (' 2 ') then
' 2 '
End) Number of certificates cancelled

There are three count, each querying the same field l.state, but the query value for this field is different, that is, in (' 0 ') in (' 1 ') in (' 2 ') algorithm equals, = ' 0 ' = ' 1 ' = ' 2 '

Here is a simple record, if you are interested, you can search for the use of case

Oracle_ Multi-field statistics (multiple count)