Search code examples
sqloracle-databaseaverage

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:

enter image description here

enter image description here

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

enter image description here

How can I do that?

Solution

  • 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