Skip to content
Advertisement

How to group distinct values and calculate fields in one SQL query

This is only my second time using Stack Overflow so I’m open to any constructive criticism on how to better format my questions.

I have a list of orders and I’m wanting to create a table of useful customer information.

I’ve created a new table that identifies unique customers (using just a select distinct of customer IDs) but I’m not sure of the proper functions to accurately group them and produce a boolean value based on their accompanying fields.

I need the new fields to display a boolean for whether or not any of the customer’s orders have been for a particular product SKU.

Imagine this is the source table

I’m wanting the output to only have unique values for each name accompanied by a boolean displaying whether or not any record of that given name has received that product.

Advertisement

Answer

Below is for BigQuery Standard SQL

If you know in advance product names (like ‘1’, ‘2’, ‘3’ in your example) and there are just few – you can use below simple version

If to apply to sample data from your question (I assume your product are of string data type here)

result is

In case if product names are not known in advance and/or number of products more than just few – below version can be handy

with exact same output

As you can see here – whole query is assembled dynamically so you don’t need to worry about number of products and their names

Below version is identical to above, but easier to manage/read

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement