CODEWITHSUNDEEP
selectgroup-bygoogle-bigqueryreferenceaggregate
Levi H.
Levi H.

Reputation: 105

How do I fix the error "select list expression [...] references which is neither grouped nor aggregated" in BigQuery?

I get the error "select list expression Opportunity.id references which is neither grouped nor aggregated" when running the following query in BigQuery:

SELECT
  Opportunity.id AS `Opportunity_Id`,
  Opportunity.testing_only__c AS `Opportunity_Testing_only`,
  MAX(DatedConversionRate.startdate) AS `DatedConversionRate_Start_date`,
FROM
  `dataset.Opportunity` Opportunity
LEFT JOIN 
  `dataset.DatedConversionRate` DatedConversionRate 
  ON DatedConversionRate.isocode = Opportunity.currencyisocode
WHERE
  DatedConversionRate.startdate < CURRENT_TIMESTAMP()

What is the problem and how can I fix it?

Upvotes: 0

Views: 10816

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Remove Opportunity.id from select or group by it:

SELECT
  Opportunity.id AS `Opportunity_Id`,
  MAX(DatedConversionRate.startdate) AS `DatedConversionRate_Start_date`,
FROM
  `dataset.Opportunity` Opportunity
LEFT JOIN 
  `dataset.DatedConversionRate` DatedConversionRate 
  ON DatedConversionRate.isocode = Opportunity.currencyisocode
WHERE
  DatedConversionRate.startdate < CURRENT_TIMESTAMP()
GROUP BY Opportunity.id

Upvotes: 1

Related Questions