How to fix this code? i tried WITH statement but it gave me an error-CodePudding

I tried to answer this question here in the code below, but it keeps giving me an error message! I've tried to figure out how to
Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales?

and it gave me this Error :

aggregate function calls cannot be nested ERD picture here

could you please help me with this?

WITH 
    account_info AS (Select * from accounts),
    orders_info AS (select * from orders),
    region_info AS (select * from region),
    sales_reps_info AS (select * from sales_reps)
    
    SELECT s.name as rep_name, r.name as region_name, MAX (SUM (o.total_amt_usd)) as total
    FROM orders_info o
    JOIN account_info a
    ON o.account_id = a.id
    
    JOIN sales_reps_info s
    ON a.sales_rep_id = s.id
    
    JOIN region_info r
    ON r.id = s.region_id
    
    GROUP BY TOTAL, REP_NAME, R.NAME
    ORDER BY 3 DESC

CodePudding user response：

When you are using the whole table there is no need for WITH

SELECT s.name as rep_name, r.name as region_name, MAX (SUM (o.total_amt_usd)) as total
    FROM orders o
    JOIN account a
    ON o.account_id = a.id
    
    JOIN sales_reps s
    ON a.sales_rep_id = s.id
    
    JOIN region r
    ON r.id = s.region_id
    
    GROUP BY TOTAL, REP_NAME, R.NAME
    ORDER BY 3 DESC
    LIMIT 100;

CodePudding user response：

I'm not sure what you are attempting with with since you don't actually define a Common Table Expression.

That aside, your query is invalid, you cannot nest aggregate functions and you are already getting the max 100 by ordering and limiting rows, so I think you just want

SELECT s.name as rep_name, r.name as region_name, SUM (o.total_amt_usd) as Total
FROM orders_info o
JOIN account_info a ON o.account_id = a.id
JOIN sales_reps_info s ON a.sales_rep_id = s.id
JOIN region_info r ON r.id = s.region_id
GROUP BY REP_NAME, R.NAME
ORDER BY Total DESC
LIMIT 100;