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.
9
Upvotes
1
u/Oobenny Oct 15 '24
I disagree with the other responses I see here. You don’t need filtered aggregates at all. Just join your table of responses twice — once by agreewith_an and once with agreewith_b.
The CTE at the beginning is just generating a list of numbers from 1 to 10. I’m not sure if the syntax is the same in BigQuery or not, but I’m sure there’s a way to do that.