By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cliente (id PRIMARY KEY) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL UNION ALL
SELECT 5 FROM DUAL UNION ALL
SELECT 6 FROM DUAL;
6 rows affected
CREATE TABLE exames (nu_ordem PRIMARY KEY, id_cliente) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 5 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL UNION ALL
SELECT 5, 6 FROM DUAL UNION ALL
SELECT 6, 1 FROM DUAL UNION ALL
SELECT 7, 1 FROM DUAL UNION ALL
SELECT 8, 4 FROM DUAL UNION ALL
SELECT 9, 5 FROM DUAL UNION ALL
SELECT 10, 3 FROM DUAL UNION ALL
SELECT 11, 6 FROM DUAL UNION ALL
SELECT 12, 2 FROM DUAL UNION ALL
SELECT 13, 1 FROM DUAL;
13 rows affected
SELECT AVG(COUNT(e.nu_ordem)) AS avg_exames_by_client
FROM cliente c
LEFT OUTER JOIN exames e
ON (c.id = e.id_cliente)
GROUP BY c.id;
| AVG_EXAMES_BY_CLIENT |
|---|
| 2.16666666666666666666666666666666666667 |
SELECT (SELECT COUNT(*) FROM exames) / (SELECT COUNT(*) FROM cliente)
AS avg_exames_by_client
FROM DUAL;
| AVG_EXAMES_BY_CLIENT |
|---|
| 2.16666666666666666666666666666666666667 |
INSERT INTO cliente (id)
SELECT 7 FROM DUAL UNION ALL
SELECT 8 FROM DUAL
2 rows affected
SELECT AVG(COUNT(e.nu_ordem)) AS avg_exames_by_client
FROM cliente c
LEFT OUTER JOIN exames e
ON (c.id = e.id_cliente)
GROUP BY c.id;
| AVG_EXAMES_BY_CLIENT |
|---|
| 1.625 |
SELECT (SELECT COUNT(*) FROM exames) / (SELECT COUNT(*) FROM cliente)
AS avg_exames_by_client
FROM DUAL;
| AVG_EXAMES_BY_CLIENT |
|---|
| 1.625 |