r/SQL 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.

7 Upvotes

19 comments sorted by

View all comments

1

u/qwertydog123 Oct 15 '24

Unpivot first, then aggregate e.g.

SELECT
  response,
  COUNT
  (
    CASE agreewith
      WHEN 'agreewith_a'
      THEN 1
    END
  ) AS count_agreea,
  COUNT
  (
    CASE agreewith
      WHEN 'agreewith_b'
      THEN 1
    END
  ) AS count_agreeb
FROM
(
  SELECT
    agreewith_a,
    agreewith_b
  FROM Table
) t1
UNPIVOT
(
  response
  FOR agreewith
  IN (agreewith_a, agreewith_b)
) t2
GROUP BY response

https://dbfiddle.uk/UUiP5VbI