BIGQUERY SELECT list expression references column CHANNEL_ID which is neither grouped nor aggregated at [10:13]

Question

I am facing this error:

BIGQUERY SELECT list expression references column CHANNEL_ID which is neither grouped nor aggregated at [10:13]

I don't know why it's caused,Can someone explain it to me?

Here are my codes:

SELECT 
    t0.CHANNEL_ID,
    t1.CHANNEL_DISPLAY_NAME
FROM
    (SELECT
         TIMESTAMP_TRUNC(_PARTITIONTIME, MONTH) as `DATE`,
         CHANNEL_ID
     FROM 
         `pops-204909.yt_kids.p_content_owner_basic_a3_yt_kids`   
     WHERE 
         DATE(_PARTITIONTIME) between "2017-08-01" and "2017-08-31"
     GROUP BY 
         `DATE`) t0
LEFT JOIN
    (SELECT
         TIMESTAMP_TRUNC(_PARTITIONTIME, MONTH) as `DATE`,
         CHANNEL_DISPLAY_NAME
     FROM 
         `pops-204909.yt_kids.p_content_owner_ad_revenue_raw_a1_yt_kids`
     WHERE 
         DATE(_PARTITIONTIME) between "2017-08-01" and "2017-08-31"
     GROUP BY 
         `DATE`) t1 ON t0.DATE = t1.DATE

Answer 1

When you use GROUP BY - all expressions in the respective SELECT list must be either those which are in the GROUP BY or should be with AGGREGATION function - like MIN, MAX etc. See more about GROUP BY clause

So in your case both sub-queries are missing respectively CHANNEL_ID and CHANNEL_DISPLAY_NAME in the respective GROUP BY. Or depends on your logic they should come with some aggregation function.

Note: Above explains the error you see. Rather than this - you query makes no much sense to me even if you will fix that specific error - but I hope you have something in mind :o)