Error with With SQL (Presto,Athena)

Asked

Viewed 18 times

0

all right? I’m having a problem with the With, I am trying to create a table grouped with a column not grouped.

In case I’m trying to assemble a table with the total transacted of each group + total of all transactions summed

The mistake that is: Column 'query.amount_group' not in GROUP BY clause

WITH query (amount_group,payment_type,brand,installments,plano)
as
(select
        sum(transactions.amount) amount_group,
        transactions."payment_type" payment_type,
        transactions."card_brand" brand,
        transactions."installments" installments,
        case
            when plans."name_plan" like '%pro%' then 'Pro'
            else 'Standart' end Plano
    from "_self_service_data"."self_transactions" transactions
    left join
        (select "customer", max("name_plan") name_plan from "_self_service_data"."self_zoop_plans" group by 1)plans
    on transactions."id_seller" = plans."customer"
    where transactions."payment_type" <> 'commission' and transactions."created_at" between date '2020-02-01' and now()
    group by 2,3,4,5)
select query.*, sum(query.amount_group) from query

Someone can help me? :)

sql

1 answer

0

I did, put an over() right after the sum(query.amount_group) to not group

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.