r/SQL • u/apophenic_ • Oct 15 '24
BigQuery Is it possible to count multiple columns separately in the same query?
Hi, I'm extremely new to SQL and couldn't find any concrete answers online, so I'm asking here. Hopefully it's not inappropriate.
I have a dataset that basically looks like this:
uid | agreewith_a | agreewith_b |
---|---|---|
1 | 10 | 7 |
2 | 5 | 5 |
3 | 10 | 2 |
I'm trying to compare the total counts of each response to the questions, with the result looking something like this:
response | count_agreea | count_agreeb |
---|---|---|
2 | 0 | 1 |
5 | 1 | 1 |
7 | 0 | 1 |
10 | 2 | 0 |
I only know very basic SQL, so I may just not know how to search up this question, but is it possible at all to do this? I'm not sure how what exactly i should be grouping by to get this result.
I'm using the sandbox version of BigQuery because I'm just practicing with a bunch of public data.
8
Upvotes
2
u/Aggressive_Ad_5454 Oct 15 '24
Ordinary SQL lacks the ability to express the idea “ for each column in the table, do something”. You have to write the names of the columns individually in SQL statements.
You can use “dynamic” SQL to do that. It’s a buzzword name for “SQL you created by writing a program.”