Skip to content
Advertisement

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?

Advertisement

Answer

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

or:

Which, for the sample data:

Both output:

AVG_EXAMES_BY_CLIENT
2.166666666666666667

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

Then the average is:

AVG_EXAMES_BY_CLIENT
1.625

db<>fiddle here

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement