group by having
--DROP TABLE BUILDING_MANAGER;
CREATE TABLE BUILDING_MANAGER
(BUILDING_NO1 VARCHAR2(3) NOT NULL ENABLE,
BUILDING_NO2 VARCHAR2(5) NOT NULL ENABLE,
MANAGER_NAME VARCHAR2(30) NOT NULL ENABLE,
SAFE_APPOINT_DE VARCHAR2(8) NOT NULL ENABLE,
SAFE_LICENSE_NO VARCHAR2(30),
REGIST_DT DATE NOT NULL ENABLE,
CONSTRAINT PK_BULDING_MANAGER PRIMARY KEY (BUILDING_NO1, BUILDING_NO2, SAFE_LICENSE_NO)
);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','200','lisa','20150923','00100123F',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','875','tyra','20150722','00109921I',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('315','123','emma','20140123','00178542K',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('211','229','sally','20120722','00153211C',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('211','229','hera','20110329','00198723B',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('100','875','sophia','20130730','00158322A',sysdate);
INSERT INTO BUILDING_MANAGER(BUILDING_NO1 ,BUILDING_NO2 ,MANAGER_NAME ,SAFE_APPOINT_DE ,SAFE_LICENSE_NO ,REGIST_DT) VALUES('315','123','wendy','20120211','00122231E',sysdate);
COMMIT;
=================================================================================================
SELECT * FROM BUILDING_MANAGER;
----------------
result:
=================================================================================================
SELECT SUBSTR(A.SAFE_APPOINT_DE, 1, 4) SAFE_APPOINT_DE, COUNT(*) CNT
FROM BUILDING_MANAGER A
GROUP BY SUBSTR(A.SAFE_APPOINT_DE, 1, 4)
ORDER BY 1 DESC;
----------------
result:
=================================================================================================
SELECT BUILDING_NO1 ,BUILDING_NO2 ,COUNT(*) CNT
FROM BUILDING_MANAGER
GROUP BY BUILDING_NO1, BUILDING_NO2;
----------------
result:
=================================================================================================
SELECT BUILDING_NO1, BUILDING_NO2, COUNT(*) CNT
FROM BUILDING_MANAGER
GROUP BY BUILDING_NO1, BUILDING_NO2
HAVING COUNT(*) > 1;
----------------
result:
