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
  ,  case
      when like '%wb%' then 'Web'
      when like '%cooo%' then 'Web'
      when like '%mobile%' then 'Mobile'

else 'K'
   end as payment_source
  , count( as Transaction_Count

, case
      when In  ('%wb%', '%cooo%',  '%mobile%') then count( * 0.35
      else count( * 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 =
where = 'CoC Utility' and payments.payment_status_id=2
and != '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( with a constant number based on the I do not have the rate stored in the DB. 

This is the error I keep getting :

ERROR: column "" 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,

    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, 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
  • 10 mths agoLast active
  • 1Replies
  • 133Views
  • 2 Following