Variable creation in SQL

Hi,

I am trying to find a way that would let fix the following code:


select 
    [payments.created_at:hest:month] as date
  ,  case
      when payment_sources.name like '%wb%' then 'Web'
      when payment_sources.name like '%cooo%' then 'Web'
      when payment_sources.name like '%mobile%' then 'Mobile'

else 'K'
   end as payment_source
  , count(payments.id) as Transaction_Count

, case
      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

from [payments+visits+payment_sources+agencies+clients]
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?

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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 Like
Like Follow
  • Status Answered
  • 2 wk agoLast active
  • 1Replies
  • 40Views
  • 2 Following