CODEWITHSUNDEEP
sqloracle-database
Olly
Olly

Reputation: 23

How to find average number of occurences (Oracle)

Table of surnames in a reference list and I need to find the average number of times a surname appears in the list, I have used the command:

SELECT column5, COUNT(*)
FROM table1
GROUP BY column5

to get a list of occurrences but there are over 800 in my database so I can manually find an average So some authors have published 9 books, some only 1, how do I find the average of this?

Upvotes: 2

Views: 2792

Answers (3)

Joshua Huber
Joshua Huber

Reputation: 3533

It is allowed to compose (i.e. nest) aggregate functions, so why not simply this:

SELECT AVG(COUNT(*)) average_occurrences
FROM table1
GROUP BY column5

Upvotes: 1

laylarenee
laylarenee

Reputation: 3284

To find the "average number of books per author", you must select the total number of books divided by the total number of authors:

SELECT CAST(COUNT(*) AS DECIMAL) / COUNT(DISTINCT column5)
FROM table1;

Note: I used the CAST ... AS DECIMAL syntax to make the result show as a decimal instead of being truncated to an integer.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You can use the AVG function try this eg:

SELECT column5, COUNT(*), avg(column5) 
FROM table1
GROUP BY column5

Upvotes: 1

Related Questions