create table a(store varchar2(10),m_class_id int,sell_class_flag int, m_id int,sell_flag int);
insert into a values('商店A', 20000,1,10000,1);
insert into a values('商店A', 20000,1,10001,1);
insert into a values('商店A', 20001,1,10002,1);
insert into a values('商店A', 20001,1,10003,1);
insert into a values('商店A', 20001,1,10004,1);
insert into a values('商店B', 20002,1,10005,1);
insert into a values('商店B', 20002,1,10006,1);
insert into a values('商店B', 20002,1,10007,1);
insert into a values('商店C', 20003,1,10008,1);
insert into a values('商店C', 20003,1,10009,1);
insert into a values('商店C', 20003,1,10010,1);
insert into a values('商店C', 20003,1,10011,1);
insert into a values('商店C', 20003,1,10012,1);
insert into a values('商店C', 20004,0,10013,1);
insert into a values('商店C', 20004,0,10014,1);
insert into a values('商店C', 20004,0,10015,1);
insert into a values('商店C', 20004,0,10016,0);
insert into a values('商店D', 20005,0,10017,0);
insert into a values('商店D', 20005,0,10018,0);
insert into a values('商店D', 20005,0,10019,0);
insert into a values('商店D', 20005,0,10020,0);
SQL>
SQL> SELECT STORE,COUNT(distinct case WHEN sell_class_flag=1 THEN m_class_ID end),SUM(sell_flag),SUM(DECODE(sell_flag,0,1,0))FROM a
2 GROUP BY STORE ORDER BY 1
3 /
STORE COUNT(DISTINCTCASEWHENSELL_CLA SUM(SELL_FLAG) SUM(DECODE(SELL_FLAG,0,1,0))
---------- ------------------------------ -------------- ----------------------------
商店A 2 5 0
商店B 1 3 0
商店C 1 8 1
商店D 0 0 4
SQL>