Table1의 Col1, Col2, Col3, Col4 으로 group by를 하고 덧붙여서 컬럼을 추가하여 조회를 하고싶습니다! Table2도 조인하여 함께 조회하고 싶습니다.(Table2에서 조회하고싶은 컬럼들의 조건들은 같습니다.) SELECT Table1.Col1 , Table1.Col2 , Table1.Col3 , Table1.Col4, , (select count(*) from Table1 where Col2 = Table1.Col2 and ColA IS NOT NULL AND TRIM (ColB) IS NOT NULL) Col5 , (select count(*) from Table1 where Col2 = Table1.Col2 and ColA IS NOT NULL AND TRIM (ColC) IS NOT NULL) Col6 , (select MAX(Col7) KEEP(DENSE_RANK FIRST ORDER BY Col2) from Table1 where Col1 = Table1.Col1 and Col2 = Table1.Col2 and Col3 = Table1.Col3 and Col4 = Table1.Col4) Col7 , (select MAX(Col8) KEEP(DENSE_RANK FIRST ORDER BY Col2) from Table1 where Col1 = Table1.Col1 and Col2 = Table1.Col2 and Col3 = Table1.Col3 and Col4 = Table1.Col4) Col8 , (select Col9 from Table2 where Col1 = Table1.Col1) Col9 , (select Col10 from Table2 where Col1 = Table1.Col1) Col10 FROM Table1 GROUP BY Table1.Col1, Table1.Col2, Table1.Col3, Table1.Col4 ORDER BY Table1.Col1, Table1.Col2, Table1.Col3, Table1.Col4 ;
SELECT a.col1 , a.col2 , a.col3 , a.col4 , SUM(COUNT(CASE WHEN a.cola IS NOT NULL THEN TRIM(a.colb) END)) OVER(PARTITION BY a.col2) col5 , SUM(COUNT(CASE WHEN a.cola IS NOT NULL THEN TRIM(a.colc) END)) OVER(PARTITION BY a.col2) col6 , MAX(a.col7) col7 , MAX(a.col8) col8 , b.col9 , b.col10 FROM table1 a LEFT OUTER JOIN table2 b ON a.col1 = b.col1 GROUP BY a.col1, a.col2, a.col3, a.col4, b.col9, b.col10 ORDER BY a.col1, a.col2, a.col3, a.col4 ;