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

611 views google-bigquery
6

I am facing this error:

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

I dont 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

answered question

1 Answer

5

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)

posted this

Have an answer?

JD

Please login first before posting an answer.