Datastore group by only returns group column, not selected columns

Leeann

I'm trying to select unique account_customers with the group by syntax, but the query doesn't return all the selected columns. It only returns the columns listed in the Group By section:

SELECT _id, account_customer, first_account_email, first_contact_bill_to_email, cf_invoice_address, "Bill to Phone number", ship_to_phone_number, cf_shipping_address FROM fs_deals_2023 WHERE upload_status <> 'Done' GROUP BY account_customer LIMIT 3

Returns
[{"account_customer": "Dr Merabet Mehdi"}, {"account_customer": "Imagine USA"}, {"account_customer": "Hotel Marsil"}]

But I was expected all the other selected columns to be returned as well, just grouped by the account_customer.

Execution ID: 955de4d4-6f5b-470a-b54b-e49f9e60e8a6
Launch ID: 1041262
Account name: 5750
Wrkflow: Account Creation & Tagging (ID: 51422)
Launch name: Launch-664eecb1

@Product

Dylan

Leeann This is expected SQL behavior.

When you use GROUP BY, every column in the SELECT clause must either be included in the GROUP BY or wrapped in an aggregate function. Since you are grouping only by account_customer, the database has no defined rule for which values of the other columns to return, so it collapses the result to just the grouped column.

If your goal is to return one row per account_customer while still selecting the other fields, you need to aggregate those fields as well, even if you are just picking an arbitrary value from each group.

For example:

SELECT
  MIN(_id) AS _id,
  account_customer,
  MIN(first_account_email) AS first_account_email,
  MIN(first_contact_bill_to_email) AS first_contact_bill_to_email,
  MIN(cf_invoice_address) AS cf_invoice_address,
  MIN("Bill to Phone number") AS bill_to_phone_number,
  MIN(ship_to_phone_number) AS ship_to_phone_number,
  MIN(cf_shipping_address) AS cf_shipping_address
FROM fs_deals_2023
WHERE upload_status <> 'Done'
GROUP BY account_customer
LIMIT 3;

This returns a single row per account_customer and satisfies SQL’s grouping rules by aggregating the remaining columns.

Nick

Going to add a quick caveat to the above provided SQL. Everything that is stated is correct. I just want to make it clear that this approach will hide any non-unique values that are returned for the fields that you used MIN() to aggregate. So, if you have a unique customer name "Cust1" which has two rows with different emails "Email1", and "Email2" this SQL will only return 1 row with "Email1". If you would like to get a row for both "Email1" and "Email2" you are better off adding all fields to the Group By section instead of aggregating the other fields.

This would look like:

SELECT
_id,
account_customer,
first_account_email,
first_contact_bill_to_email,
cf_invoice_address,
"Bill to Phone number",
ship_to_phone_number,
cf_shipping_address
FROM fs_deals_2023
WHERE upload_status <> 'Done'
GROUP BY _id, account_customer, first_account_email,
first_contact_bill_to_email,
cf_invoice_address,
"Bill to Phone number",
ship_to_phone_number,
cf_shipping_address
LIMIT 3