How to avoid using nested aggregate functions?

Question

I need some help here, I'm sure you guys know how to do it: Let's start with table structure:

author(name, nationality, Gender);
article(title, year, conference);
publication(articleTitle, authorName);

I need to know the Gender of authors which have the highest number of publications. By the Way I'm using PostgreSQL, don't know if that matters.

Here's My idea:

select gender from author
join publication on(name = authorName)
having (count(articleTitle) = max(count(articleTitle)))
group by gender

Now, I know i cannot use nested aggregate functions, and that's why I'm trying to use nested selects, something like select gender where gender in (another select) But I did not managed to avoid the aggregate function issue. Hope you can help me, Thank you

Sample data and desired results would help.

Gordon Linoff
– Gordon Linoff

2018-05-31 10:53:52 +00:00
Commented May 31, 2018 at 10:53 — Gordon Linoff
– Gordon Linoff, Commented May 31, 2018 at 10:53

Gordon Linoff · Accepted Answer · 2018-05-31 10:55:29Z

1

This query gets you the authors, ordered by the number of publications:

select a.name, a.gender, count(*) as num_publications
from author a join
     publication p
     on a.name = p.authorName
group by a.name, a.gender
order by num_publications desc;

If you want the top three, then use fetch first or limit:

select a.name, a.gender, count(*) as num_publications
from author a join
     publication p
     on a.name = p.authorName
group by a.name, a.gender
order by num_publications desc
fetch first 3 rows only;

answered May 31, 2018 at 10:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to avoid using nested aggregate functions?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related