How to get mean of exams by client with 2 tables?

I know a little bit of sql, only the basic, now I need to create a analytic query but can’t do this yet.

I have 2 tables on my db oracle, client and exams:

I am tried a lot of ways to get the mean of exams by client, but no success yet.4

The result expected is:

exams = 13

clients = 6

13/6= 2.166666666…7

How can I do that?

Answer

If you have clients who have not taken any exams then you want:

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;

or:

SELECT (SELECT COUNT(*) FROM exames) / (SELECT COUNT(*) FROM cliente)
         AS avg_exames_by_client
FROM   DUAL;

Which, for the sample data:

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;

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;

Both output:

AVG_EXAMES_BY_CLIENT
2.166666666666666667

If you then add a couple of clients but no more exams:

INSERT INTO cliente (id)
SELECT 7 FROM DUAL UNION ALL
SELECT 8 FROM DUAL

Then the average is:

AVG_EXAMES_BY_CLIENT
1.625

db<>fiddle here