Division in postgres

Admin 1
Viewed 4406

I've 2 tables, Paper and Author. Paper contains all the papers published in a given time period and Authors has the name of author in one column and id of paper published in another(so, the same author may occur several times). Now I'm supposed to find the average number of authors per paper.

My try:-

    (SELECT COUNT(*) FROM Author as decimal)/(SELECT COUNT(*) FROM Paper);

Clearly the first part will give me the number of rows in table Author and the second gives me the total no. of papers published. But, I'm getting an error "ERROR: syntax error at or near "/"". What I believe is that it's because I'm probably dividing a table by a table(since I'm just a beginner in postgres, I may be horribly wrong, please correct me if that's the case). I just wanna know how can we solve this error. Thanks!

postgresql sql
2 Answers

Assuming all papers have at least one author:

select count(*) * 1.0 / count(distinct paper_id)
from authors;

The * 1.0 is to avoid integer division.

user_11440350

You can join the tables and divide the specific information for example:

SELECT 
COUNT(au.*)/COUNT(pa.*) AS division_result      -- depends on your data

FROM Author au

JOIN Paper pa
ON                               -- join criteria depends on your data(eg.au.id=pa.id  )

Hope this helps (I'm assuming you are not using BigQuery) Sincerely.

user_109290800
Related