sqlgoogle-bigquery

Bigquery - Select a column with not grouping them in group by clause

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

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.

Solution

  • Technically, you can envelope device_category and user_type with ANY_VALUE or MAX or MIN:

     SELECT
  month, 
  year, 
  ANY_VALUE(device_category), 
  ANY_VALUE(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;