Skip to content
Advertisement

Defining a subtable and then query from that table using SQL

I have a table with many columns, and I want to count the unique values of each column. I know that I can do

Here sho is the table and sho_01,…. are the individual columns. This is BigQuery by the way, so they use UNION ALL.

Next, I want to do the same thing, but for a subset of sho, say SELECT * FROM sho WHERE id in (1,2,3). Is there a way where I can create a subtable first, and then query the subtable? Something like this

Thanks

Advertisement

Answer

Presumably, the columns are all of the same type. If so, you can simplify this using arrays:

You can then easily filter however you want by adding a where clause before the group by.

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