CODEWITHSUNDEEP
google-bigquery
nidhi
nidhi

Reputation: 329

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.

Upvotes: 1

Views: 3315

Answers (2)

Sergey Geron
Sergey Geron

Reputation: 10172

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;

Upvotes: 3

saifuddin778
saifuddin778

Reputation: 7277

You can use a subquery to achieve this:

SELECT 
  x.month, 
  x.year, 
  x.device_category, 
  x.user_type, 
  x.product_name, 
  ROUND(SUM(x.item_revenue),2) as item_revenue 
FROM 
 (SELECT
    month,
    year,
    device_category,
    user_type,
    product_name,
    item_revenue
  FROM `ProjectName.DatasetName.GA_REPORT_3_*` 
  WHERE _table_suffix BETWEEN '20201101' and '20210131' 
  AND channel_grouping = 'Organic Search'
 ) x 
GROUP BY 
  x.month, 
  x.year,  
  x.product_name, 
  x.device_category, 
  x.user_type 
ORDER BY ROUND(SUM(x.item_revenue),2) DESC;

Upvotes: 0

Related Questions