Variable creation in SQL
I am trying to find a way that would let fix the following code:
[payments.created_at:hest:month] as date
when payment_sources.name like '%wb%' then 'Web'
when payment_sources.name like '%cooo%' then 'Web'
when payment_sources.name like '%mobile%' then 'Mobile'
end as payment_source
, count(payments.id) as Transaction_Count
when payment_sources.name In ('%wb%', '%cooo%', '%mobile%') then count(payments.id) * 0.35
else count(payments.id) * 1.0
end as CB_Revenue
, [sum(payments.amount):$] as Total_Amount
join service_types on payments.service_type_id = service_types.id
where clients.name = 'CoC Utility' and payments.payment_status_id=2
and service_types.name != 'fee'
and [payments.created_at:hest:month] = [now():month] - '1 month'::interval
group by 1, 2
The code in red is what I am trying to fix. My goal is to multiply the number of the transaction under one category count(payments.id) with a constant number based on the payment_sources.name. I do not have the rate stored in the DB.
This is the error I keep getting :
ERROR: column "payment_sources.name" must appear in the GROUP BY clause or be used in an aggregate function Line: 4 Position: 129 (Expanded Line: 25 Position: 12)
and group by won't fix it cuz I am using the count function which can't be used in a group by clause.
Any thoughts please?
HI Alaa ! Thanks for reaching out. If you do
group by 1, 2, payment_sources.name
Do you get the results you are looking for? Since your select statement has aggregate functions, we need to group by any non-aggregated fields. Note that group by 1, 2, payment_sources.name will still work with your count function
Feel free to chat our support team using the chat icon at the bottom corner of your screen in-app as well and we can further assist!Reply