How to include three or more aggregators in a sql query?

Question

I have a table called retail which stores items and their price along with date of purchase. I want to find out total monthly count of unique items sold.

This is the sql query I tried

select date_trunc('month', date) as month, sum(count(distinct(items))) as net_result from retail group by month order by date;

But I get the following error

ERROR:  aggregate function calls cannot be nested

Now I searched for similar stackoverflow posts one of which is postgres aggregate function calls may not be nested and but I am unable to replicate it to create the correct sql query.

What am I doing wrong?

George S · Accepted Answer · 2018-12-09 22:45:59Z

1

From your description, it doesn't seem like you need to nest the aggregate functions, the count(distinct item) construction will give you a count of distinct items sold, like so:

select date_trunc('month', date) as month
 , count(distinct items) as unique_items_sold
 , count(items) as total_items_sold
from retail 
group by "month" 
order by "month" ;

If you had a column called item_count (say if there was row in the table for each item sold, but a sale might include, say, three widgets)

select date_trunc('month', date) as month
 , count(distinct items) as unique_items_sold
 , sum(item_count) as total_items_sold
from retail 
group by "month" 
order by "month" ;

answered Dec 9, 2018 at 22:45

George S

2,19114 silver badges14 bronze badges

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

1 Comment

Souvik Ray Over a year ago

Yep this is what I needed!

Eriks Klotins · Accepted Answer · 2018-12-09 21:39:05Z

0

Use subqueries:

Select month, sum(citems) as net_result 
   from 
       (select 
           date_trunc('month', date) as month, 
           count(distinct(items)) as citems 
        from 
           retail 
        group by month 
        order by date
        )

answered Dec 9, 2018 at 21:39

Eriks Klotins

4,1701 gold badge14 silver badges27 bronze badges

Comments

dwir182 · Accepted Answer · 2018-12-10 01:18:19Z

0

I am suspect your group by statement will throw an Error because your month column are condition column and you cannot put in the same level in your query so put your full expression instead.

select
  month,
  sum(disct_item) as net_results
from
  (select 
     date_trunc('month', date) as month, 
     count(distinct items) as disct_item
   from 
     retail 
   group by 
     date_trunc('month', date)
   order by 
     date) as tbl
group by
  month;

You cannot make nested aggregate so you wrap first count to subquery and after that in outer you make sum to do the operation.

answered Dec 10, 2018 at 1:18

dwir182

1,54911 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to include three or more aggregators in a sql query?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related