SQL Aggregate Function Nesting

Question

I am learning SQL with PostgreSQL, and have run into a problem regarding nested Aggregate functions.

I am trying to find the details of private owners with the maximum amount of properties, where I have two relations, privateowner and propertyforrent with propertyforrent having a foreign key, ownwerno.

I suspect that my issue is where I am attempting to nest Aggregate Functions, but I cannot see a way around it.

NOTE:- the database I am using has a typo in the attribute ownwerno in propertyforrent, where it should be ownerno.

The code I am attempting to use is shown below~:-

SELECT o.fname, o.lname, telno
FROM privateowner o
WHERE o.ownerno = (SELECT p.ownwerno
           FROM propertyforrent p
           HAVING COUNT(p.ownwerno) = MAX(COUNT(o.ownerno)));

Its accompanying error is as follows:-

ERROR:  column "p.ownwerno" must appear in the GROUP BY clause or be used in a
aggregate function
LINE 3: WHERE o.ownerno = (SELECT p.ownwerno
                                  ^


********** Error **********

ERROR: column "p.ownwerno" must appear in the GROUP BY clause or be used in an
aggregate function
SQL state: 42803
Character: 78

Any insight would be wonderful.

score 5 · Accepted Answer · 2012-11-30 18:08:22Z

PostgreSQL 9.1 Schema Setup:

create table privateowner(ownerno integer, fname text);
insert into privateowner(ownerno, fname) values (1,'Alice'), 
                                                (2,'Bob'), 
                                                (3,'Charlie');

create table propertyforrent(ownerno integer);
insert into propertyforrent(ownerno) values (1), (2), (2), (3), (3);

Query 1:

with w as ( select ownerno, count(*) as property_count 
            from propertyforrent 
            group by ownerno )
select * 
from privateowner
where ownerno in( select ownerno 
                  from w 
                  where property_count=( select property_count
                                         from w 
                                         order by 1 desc limit 1) )

Results:

| OWNERNO |   FNAME |
---------------------
|       2 |     Bob |
|       3 | Charlie |

see this on SQL Fiddle

inspired by @araqnid's answer (+1), here is another variant with windowing functions:

Query:

select ownerno, fname
from( select ownerno, fname, rank() over (order by count(*) desc) rnk
      from privateowner join propertyforrent using(ownerno) 
      group by ownerno, fname ) z
where rnk=1

Results:

| OWNERNO |   FNAME |
---------------------
|       3 | Charlie |
|       2 |     Bob |

see this on SQL Fiddle

Gordon Linoff · Accepted Answer · 2012-11-30 15:46:31Z

2

The problem is that you cannot nest aggregation functions.

What you want is something more like the following:

SELECT o.fname, o.lname, telno
FROM privateowner o
WHERE o.ownerno = (SELECT p.ownwerno
                   FROM propertyforrent p
                   GROUP BY p.owerno
                   order by COUNT(*)
                   limit 1
                  )

However, I would prefer the query with the explicit join:

select o.fname, ollname, o.telno, numProperties
from PrivateOwner o join
     (select p.ownerno, count(*) as numProperties
      from PropertyForRent pft
      group by p.owerno
     ) pfr
     on o.ownerno = pfr.ownerno
order by NumProperties
limit 1

Using the join, you can also add in the number of properties. And, you can also choose more than one. To get all the properties with the maximum, you can use the following where clause:

where NumProperties = (select count(*) as NumProperties
                       from PropertyForRent
                       group by owerno
                       order by 1 desc
                       limit 1
                      )

edited Nov 30, 2012 at 15:46

answered Nov 30, 2012 at 15:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

6 Comments

user533832 Over a year ago

+1 though I've interpreted "the details of private owners with the maximum amount of properties" as meaning there might be a tie and in that case all owners would need to be listed.

Miles Over a year ago

Indeed, if there are two owners with the same amount of properties but that amount is the maximum, then both would need to be listed. Good answer though :)

Miles Over a year ago

@JackDouglas Thanks for the response, in the first variation of the query, what does the 'limit 1' do?

user533832 Over a year ago

@VisionIncision did you mean to ask Gordon? It means the subquery only returns the first row.

Miles Over a year ago

@Gordon Linoff Yes, sorry. Ah ok, thanks. Is there a quick way to get the set of tuples that satisfy the condition?

|

araqnid · Accepted Answer · 2012-11-30 17:29:31Z

2

This is a situation where window functions work well, although if you're just starting out in SQL you might want to come back to this when you're more familiar with the basics.

select ownerno, fname
from (
    select privateowner.*, rank() over(order by property_count desc)
    from privateowner join (
            select ownerno, count(*) as property_count
            from propertyforrent group by ownerno
        ) owner_stats using (ownerno)
) x
where x.rank = 1

answered Nov 30, 2012 at 17:29

araqnid

135k25 gold badges164 silver badges139 bronze badges

4 Comments

user533832 Over a year ago

+1 much better than mine: I've pinched the basic idea and updated my answer.

user533832 Over a year ago

also, clever use of the default column name pg gives the rank: is that something you'd generally recommend (ie do you think it is a feature unlikely to change in a future release?)

araqnid Over a year ago

I imagine it is reliable, however I would automatically explicitly name the column in real production code (just like I would expand the ".*" too)

Erwin Brandstetter Over a year ago

Explicit alias >> automatically derived alias. Better documentation, easier to understand, more reliable.

Collectives™ on Stack Overflow

SQL Aggregate Function Nesting

3 Answers 3

Comments

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related