CODEWITHSUNDEEP
sqlpostgresql
uhuuyouneverknow
uhuuyouneverknow

Reputation: 433

aggregate function calls cannot be nested postgresql

I get "aggregate function calls cannot be nested" error from PostgreSQL. I tried different bunch of things but could not solve.

select c.*, (
             select sum((count(distinct product_id))/2)
             from page_views
             where product_id in (c.p1, c.p2)
             group by user_id, session_id
             having count(distinct product_id) > 1
            ) freq
from (
      select a.product_id p1, b.product_id p2
      from (select distinct product_id from page_views) a,
           (select distinct product_id from page_views ) b
      where a.product_id <> b.product_id
     ) c ;

Thanks!

Upvotes: 8

Views: 45358

Answers (3)

Akshaya Natarajan
Akshaya Natarajan

Reputation: 2171

I don't completely understand what you are trying to do in this example, however I would like to show an example of how I used multiple aggregating functions in PostgresQL.

Suppose my goal is to find maximum of max(time) - min(time): 

select max(a.trip_time) from 
(  select trip_id, max(time) - min(time) as trip_time 
   from gps_data 
   where date = '2019-11-16' 
   group by trip_id) as a;

I hope this is clear!

Upvotes: 2

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5950

You can just use subselect to get nested aggregate functions, like this:

select c.*, (SELECT sum(count_column) FROM (
                 select (count(distinct product_id))/2 AS count_column
                 from page_views
                 where product_id in (c.p1, c.p2)
                 group by user_id, session_id
                 having count(distinct product_id) > 1
               ) sub_q
            ) freq
from (
      select a.product_id p1, b.product_id p2
      from (select distinct product_id from page_views) a,
           (select distinct product_id from page_views ) b
      where a.product_id <> b.product_id
     ) c ;

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you want to count users who have seen two pages in a single senssion, then this is the query:

select v1.product_id, v2.product_id, count(distinct v2.user_id)
from page_views v1 join
     page_views v2
     on v1.user_id = v2.user_id and v1.session_id = v2.session_id and
        v1.product_id < v2.product_id
group by v1.product_id, v2.product_id;

This is the most sensible interpretation I can imagine of the actual intent.

Upvotes: 1

Related Questions