SQL compilation error: error line 1 at position 7 'XXXXXX' in select clause is neither an-CodePudding

select a_cents, a_currency, ba_id, em_name, cre_at, em_date, received_at, declined_at, declined_reason, declined_at as da, month (da) as mois, year (da) as annee

from db_api.CHECKS
where declined_reason = 1 and 2
group by mois and annee
order by declined_at desc

Hi, I'm trying a pivot table or sort by taking the month and year from the "declined_at" column as "month" and "year", but I get this error.

Thanks for your help

CodePudding user response：

If you have a GROUP BY then all columns in your SELECT list need to either be in your GROUP BY or be aggregate functions.

It’s not obvious what you are trying to achieve with your GROUP BY statement, so if you need more help please update your question with sample data and the result you want to achieve

CodePudding user response：

Your query as written should not even process as you might expect. Readability also helps to see the WHAT you want, FROM, and WHERE components such as revised.

select 
      a_cents, 
      a_currency, 
      ba_id, 
      em_name, 
      cre_at, 
      em_date, 
      received_at, 
      declined_at, 
      declined_reason, 
      declined_at as da,  
      month (da) as mois, 
      year (da) as annee
   from 
      db_api.CHECKS     
   where 
      declined_reason IN ( 1, 2 )             
   group by 
      month (da), 
      year (da)
   order by 
      declined_at desc

Your "WHERE" clause had where "declined_reason = 1 and 2" is probably being interpreted as

where ( declined_reason = 1 ) AND ( 2 )

in which the (2) is always true as it is not zero or false. This would thus only give you records with a declined_reason = 1. I change the query to indicate that the "declined_reason in ( 1, 2 )", meaning I want the declined_reason to have a value that is found within the list provided in parenthesis, thus includedin 1 OR 2 as valid.

As for a GROUP by, groups typically mean there is some sort of aggregation, count(), sum(), min(), max(), avg(), etc., and a group by must include all columns that are NOT part of an aggregation. Such as listing all sales in an electronics store by category. You could have 100's of sales in respective of computer, tv, appliances, video games, sound systems, etc. So, the SalesCategory, SUM(salesAmount) as TotalSales GROUP BY SalesCategory. You have no discernable consideration of an aggregate.

Please EDIT your existing post to better clarify what it is you are TRYING to get out of your data.