I'm having day-wise tables with google analytics data that is split based on device_category(desktop/mobile/tablet) and user_type(new user/returning user).
My requirement is, to query for the top-performing product in the month and just know the type of device and user. I do not want to group them based on device_category, user_type.
When excluding them from my query is gives an error saying - "Query error: SELECT list expression references column device_category which is neither grouped nor aggregated at [3:21]"
QUERY THAT DOES NOT WORK(this is my requirement)
SELECT
month,
year,
device_category,
user_type,
product_name,
round(sum(item_revenue),2) as item_revenue
FROM
`ProjectName.DatasetName.GA_REPORT_3_*`
where
_table_suffix between '20201101' and '20210131'
and channel_grouping = 'Organic Search'
group by
month,
year,
channel_grouping,
product_name
order by
item_revenue desc;
QUERY THAT WORKS
SELECT
month,
year,
device_category,
user_type,
product_name,
round(sum(item_revenue),2) as item_revenue
FROM
`ProjectName.DatasetName.GA_REPORT_3_*`
where
_table_suffix between '20201101' and '20210131'
and channel_grouping = 'Organic Search'
group by
month,
year,
channel_grouping,
product_name,
device_category,
user_type
order by
item_revenue desc;
Sample Data
I know in regular SQL workbenches we can select a Column in SQL not in Group By clause, but the same does not work for my issue on Bigquery.
Could you help me with a workaround for this.
user_type
ordevice_category
in the grouping this means that your calculationsum(item_revenue)
will sum "over the top of them". Meaning, it will include multipleuser_types
anddevice_category
in thesum(item_revenue)
calculation. So if you select them, but don't group by them, what values would you expect to see in these columns? There will be multiple values for them in each row... I suggest that you go through the exercise of trying to create a spreadsheet with the results you want, then you'll see what I mean. - Simon D