데이터베이스 질문과 답변
SELECT COUNT(D.dep_no) as "사원"
FROM employee D
right outer join dept E
ON D.dep_no = E.dep_no
GROUP BY E.dep_name;
SELECT B.dep_name AS "부서"
, COUNT(C.emp_no) AS "고객"
FROM employee A
right outer join dept B
ON A.dep_no = B.dep_no
left outer join customer C
ON A.emp_no = C.emp_no
group by B.dep_name;
이 두개 쿼리를 합치려고 하는데
SELECT B.dep_name AS "부서"
, (SELECT COUNT(D.dep_no)
FROM employee D
right outer join dept E
ON D.dep_no = E.dep_no
GROUP BY E.dep_name
) AS "사원"
, COUNT(C.emp_no) AS "고객"
FROM employee A
right outer join dept B
ON A.dep_no = B.dep_no
left outer join customer C
ON A.emp_no = C.emp_no
group by B.dep_name;
이렇게 하니까 스칼라서브쿼리쪽에 데이터가 4개가 들어있어서 오류가 나는데
어떻게 수정해야 할지...모르겠어서 고수님덜...부탁드립니다.
SELECT d.dep_name
, COUNT(DISTINCT e.emp_no) emp_cnt
, COUNT(c.emp_no) cust_cnt
FROM dept d
LEFT OUTER JOIN emp e
ON d.dep_no = e.dep_no
LEFT OUTER JOIN customer c
ON e.emp_no = c.emp_no
GROUP BY d,dep_no, d.dep_name
ORDER BY d,dep_no
;
와우...감사합니다...
) 버튼을 클릭하여 작성 하시면 됩니다.