|
WITH TB AS (
SELECT 'AA' IDX, TO_CHAR(SYSDATE-3, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'AA' IDX,TO_CHAR(SYSDATE-3, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'AA' IDX,TO_CHAR(SYSDATE-3, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'AA' IDX,TO_CHAR(SYSDATE-2, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'AA' IDX,TO_CHAR(SYSDATE-2, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'AA' IDX,TO_CHAR(SYSDATE-1, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'BB' IDX, TO_CHAR(SYSDATE-3, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'BB' IDX,TO_CHAR(SYSDATE-3, 'YYYYMMDD') DD FROM DUAL
UNION ALL
SELECT 'BB' IDX,TO_CHAR(SYSDATE-1, 'YYYYMMDD') DD FROM DUAL
)
SELECT IDX
, SUM (CNT) CNT
, COUNT (*) DCNT
FROM ( SELECT IDX
, DD
, COUNT (*) CNT
FROM TB
GROUP BY IDX
, DD)
GROUP BY IDX
위의 쿼리를 실행하면 아래처럼 나오는데요. 근데 GROUP BY 를 두번 써야 하는데 GROUP BY를 한번만 사용해서 건수/일수를 구할 수 있을까요?
|
