How can I apply group by subquery like main query ?
This is the query :
select a.clinic_no ,
b.CLINIC_DESC_A ,
a.doctor_no ,
c.STAFF_NATIVE_NAME ,
count(DISCHARGE_FROM_CLINIC) ,
(select count(patient_no) from trng.opd_visits_history WHERE event_date BETWEEN 20240815 and 20240822) as "Total"
from trng.opd_visits_history a , trng.hospital_clinics b , trng.hospital_staff c
WHERE event_date BETWEEN 20240815 and 20240822
and a.CLINIC_NO = b.CLINIC_NO
and a.DOCTOR_NO = c.STAFF_NO
and b.DOCTOR_NO = c.STAFF_NO
and a.HOSPITAL_NO = 720022
and b.HOSPITAL_NO = 720022
and c.HOSPITAL_NO = 720022
AND a.DISCHARGE_FROM_CLINIC = 1
group by a.clinic_no , a.doctor_no , b.CLINIC_DESC_A , c.STAFF_NATIVE_NAME
The output like this :
clinic no doctor no discharge from clinic total
10 22 5 1230
12 15 4 1230
16 19 7 1230
20 13 2 1230
I need to group by subquery like this :
select count(patient_no) from trng.opd_visits_history WHERE event_date BETWEEN 20240815 and 20240822 group by clinic_no , doctor_no
when I use group by inside with subquery it show error
ORA-01427 single row subquery returns more than one row
You're trying to use a correlated subquery, but - you didn't apply any correlation between its table to outside table(s). It must return only one row (value), but - when you put group by
clause into it - it caused more than a single row to be returned and you got an error.
Something like this might do (I also JOIN
ed tables on common columns and let WHERE
clause to filter resulting rows):
SELECT a.clinic_no,
b.clinic_desc_a,
a.doctor_no,
c.staff_native_name,
COUNT (discharge_from_clinic),
(SELECT COUNT (patient_no)
FROM trng.opd_visits_history h
WHERE h.clinic_no = a.clinic_no --> this
AND h.doctor_no = a.doctor_no --> this
AND event_date BETWEEN 20240815 AND 20240822) AS "Total"
FROM trng.opd_visits_history a
JOIN trng.hospital_clinics b
ON a.clinic_no = b.clinic_no
AND a.hospital_no = b.hospital_no
JOIN trng.hospital_staff c
ON a.doctor_no = c.staff_no
AND b.doctor_no = c.staff_no
AND c.hospital_no = a.hospital_no
WHERE event_date BETWEEN 20240815 AND 20240822
AND a.hospital_no = 720022
AND a.discharge_from_clinic = 1
GROUP BY a.clinic_no,
a.doctor_no,
b.clinic_desc_a,
c.staff_native_name