Column X which is neither GROUPED nor AGGREGATED – StandardSQL / BigQuery

I’ve been reading posts about this and the so many suggested solutions didn’t work as I want. I’m trying to get the sum of some columns for the same rows then selecting all the other columns when running my query.

For example, when trying to use group by for one column, it doesn’t work. As suggested in many posts/questiosn on stackoverflow, the solution is to group by all the columns in the select part, which doesn’t return the desired results.

Well, here is my problem,

I’m trying to run the following query:

SELECT
  date,
  campaign_id,
  ad_name,
  NOM_ENSEMBLE,
  SUM(impressions) AS IMPRESSIONS,
  SUM(clicks) AS CLICKS,
  SUM(spend) AS SPEND,
  Frequentation

FROM
  `my-project.dugut.for_analysis`

I have the following columns mentionned above and I got for example for the same NOM_ENSEMBLE for different impressions and clicks and I want to create a table that contains the “NOM_ENSEMBLE” and “DATE” with the sum of impressions, clicks, spend for the same DATE/NOM_ENSEBMLE!

Answer

I want to create a table that contains the “NOM_ENSEMBLE” and “DATE” with the sum of impressions, clicks, spend for the same DATE/NOM_ENSEBMLE!

If you are doing aggregation, you need a GROUP BY. The GROUP BY should contain only the unaggregated columns in the SELECT. So, the query should look like this:

SELECT date, NOM_ENSEMBLE,
       SUM(impressions) AS IMPRESSIONS,
       SUM(clicks) AS CLICKS,
       SUM(spend) AS SPEND
FROM `my-project.dugut.for_analysis`
GROUP BY date, NOM_ENSEMBLE;