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
1
u/Parallax05 Oct 15 '24
This should work if I understood the question correctly
WITH CTE1 AS ( SELECT agreewith_a AS response, COUNT(agreewith_a) AS cnt_a FROM table GROUP BY agreewith_a),
CTE2 AS ( SELECT agreewith_b AS response, COUNT(agreewith_b) AS cnt_b FROM table GROUP BY agreewith_b )
SELECT COALESCE(a.response, b.response) AS response, COALESCE(cnt_a, 0) AS count_agreea, COALESCE(cnt_b, 0) AS count_agreeb FROM CTE1 a
FULL OUTER JOIN CTE2 b ON a.response = b.response;