Unnest Expression References Column Which Is Neither Grouped Nor Aggregated

UNNEST expression references column which is neither grouped nor aggregated

My first Answer is for original version of this question.

When I answered, I realized you have changed it to quite different one :o)

So below answer is for most recent version of your question:

I think that in "alternative" version you just do not need GROUP BY at all, because you operate on original (un-flattened) row by row and for each row (visitId) you calculate firstHitHour

SELECT
  visitId,
  (SELECT MIN(hour) FROM UNNEST(hits)) as firstHitHour
FROM
  `my-table.ga_sessions_20161122`

In your initial query - you kind of flattening all records for each row - so that's why you need then to group them back

UNNEST expression references column products which is neither grouped nor aggregated at

#standardSQL
SELECT 
  user_id,
  MIN(TIMESTAMP) AS first_order_time,
  MAX(TIMESTAMP) AS most_recent_order_time,
  COUNT(1) AS no_orders,
 SUM((SELECT SUM((item_price - extra_discount_total) / IFNULL(exchange_rate,1)) 
  FROM UNNEST(products))
 ) AS sums
FROM `order` 
WHERE parent_order_id IS NULL
GROUP BY 1
HAVING MIN(TIMESTAMP)<TIMESTAMP('2017-12-31') AND MAX(TIMESTAMP)>TIMESTAMP('2018-01-01')

PARTITION BY expression references cust.cif which is neither grouped nor aggregated at [23:60]

The issue is because every column has to be in the group by, including those used in a window function, and right now cust.cif isn't being included. Depending on what you're wanting, maybe you can include it in the group by and still get what you're after.

Another solution that I normally use when I want to combine aggregate and window functions is just to use a window function for my aggregate function, and then you don't have to worry how the group by is affecting your results.

SELECT friend.customer
        , SUM(COALESCE(cbal.total_balance, 0)) over (PARTITION BY friend.customer, full_date, balance_tier) AS total_balance
        , full_date
        , balance_tier
        , dense_rank() OVER (PARTITION BY friend.customer, cust.cif ORDER BY cbal.full_date DESC) rn_desc
FROM friend
LEFT JOIN cbal
  ON friend.customer = cbal.customer_id
LEFT JOIN cust
  ON friend.customer = cust.customer_id

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

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)

SELECT list expression references column user_id which is neither grouped nor aggregated at [8:5]

Why not just do this?

SELECT e.medical_id,
       COUNT(DISTINCT e.Virus_id) AS Virus_Exposure,
       COUNT(e.EndoCrin_id) AS Medical_Delivery,
       COUNT(e.site_id_clinic) AS Number_of_Site
FROM `endo-2` e JOIN
     `xp-56` x
     ON x.medical_id = e.medical_id
WHERE e._PARTITIONTIME BETWEEN TIMESTAMP("2017-12-15") AND TIMESTAMP("2018-01-10")
GROUP BY e.medical_id;

---

Related Topics