Redshift - Aggregate function calls may not have nested aggregate or window function

Question

I am trying to build a SQL query that would count the sum of sales made based on certain values as shown below:

Given below is how my dataset is:

cust_name,sales_count,day_count
cust_a,100,3
cust_a,200,5
cust_a,150,7
cust_a,120,1
cust_a,180,10
cust_a,100,8
cust_b,20,3
cust_b,10,4
cust_b,50,6
cust_b,60,8
cust_b,15,9

I would like to get the output in the below format

cust_name,sales_count,day_count
cust_a,280,last_14
cust_a,450,last_7
cust_b,85,last_14
cust_b,80,last_7

Given below is the case statement I tried to build

select cust_name, 
       sum(case when day_count > 7 then count(sales_count) else 0 end) as count_14,
       sum(case when day_count < 7 then count(sales_count) else 0 end) as count_7
from sales
group by cust_name;

I am using a Amazon Redshift Database.

Found a similar issue in this link (Amazon Redshift - Get week wise sales count by category) but I keep getting aggregate function calls may not have nested aggregate or window function.

Could anyone help trouble shoot this. Thanks.

Answer 1

From your question, you can try this query.

use SUM and CASE WHEN Expression.

select cust_name, 
       sum(case when day_count > 7 then sales_count else 0 end) as count_14,
       sum(case when day_count < 7  then sales_count else 0 end) as count_7
from sales
group by cust_name;

EDIT:

Becasue Aggregate functions can't nest multiple times.

If you want to fix

sum(case when day_count > 7 then count(sales_count) else 0 end) 

You can try to write a subquery to fix it.

SELECT cust_name,
       sum(case when day_count > 7 then cnt else 0 end) as count_14,
       sum(case when day_count < 7 then cnt else 0 end) as count_7
FROM (
    SELECT cust_name,(case when day_count > 7 then 1
                           when day_count < 7 then 2
                           else null
                      end) grp,
          count(sales_count) cnt
    FROM sales
    GROUP BY cust_name,
             (case when day_count > 7 then 1
                   when day_count < 7 then 2
                   else null
             end)
)t 
WHERE grp is not null
GROUP BY cust_name

Answer 2

to produce the desired output, what you need is just

sum(case when day_count > 7 then sales_count else 0 end)

what you have in the brackets is the expression which output you redirect to the sum function that aggregates it, so for cust_a it produces the following set of values:

cust_a,100,3 -> 0 (3<=7)
cust_a,200,5 -> 0 (5<=7)
cust_a,150,7 -> 0 (7<=7)
cust_a,120,1 -> 0 (1<=7)
cust_a,180,10 -> 180 (10>7)
cust_a,100,8 -> 100 (8>7)

and then the sum is 280