i want to select multiple columns and group the email with GROUP BY
#standardSQL
SELECT
customers.orderCustomerEmail AS email,
customers.orderCustomerNumber AS customerNumber,
customers.billingFirstname AS billingFirstname,
customers.billingLastname AS billingLastname
FROM dim_customers AS customers
GROUP BY customers.orderCustomerEmail
Fails with:
Error: SELECT list expression references customers.orderCustomerNumber
which is neither grouped nor aggregated at [4:7]
This is similar to this question Bigquery select distinct values
but it doesn't solve my problem because its a different result adding all columns to GROUP BY
same with SELECT DISTINCT
dim_customer schema:
orderCustomerEmail:STRING,
billingFirstname:STRING,
billingLastname:STRING,
orderCustomerNumber:STRING,
OrderNumber:STRING
dummy data: https://docs.google.com/spreadsheets/d/1T1JZRWni18hhU4tO-9kQqq5Y3hVWgpP-aE7o6ij9bDE/edit?usp=sharing
Best Answer
When you group by some columns, you need to make sure you apply some aggregation function(s) to the rest of the columns. otherwise you get exactly error that you show in your question
Try below example for BigQuery Standard SQL
or with just simple DISTINCT
Please note: your question is not specific enough in terms of what exactly output you expect, so above will most likely require some adjustment to your specific needs
Update
Update
dim_customer schema: