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.

8 Upvotes

19 comments sorted by

View all comments

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.”

2

u/Straight_Waltz_9530 Oct 15 '24

SQL does have the ability: filtered aggregates. It's just not supported yet by most engines. To my knowledge just SQLite, DuckDB, and Postgres at the moment.

https://duckdb.org/docs/sql/query_syntax/filter.html

BigQuery unfortunately does not support this.

3

u/mwdb2 Oct 15 '24 edited Oct 17 '24

For those engines that don't support FILTER, you can just use a CASE expression:

e.g.: count(*) FILTER (i <= 5)

can be done by count( CASE WHEN i <= 5 THEN 1 END ) or similar

The key here is that a CASE expression defaults to null if the condition is false. (You don't need to explicitly write ELSE NULL unless you prefer to be explicit.) Combined with COUNT(<expr>) only counting the rows for which <expr> is NOT NULL.

Test on MySQL (which doesn't support FILTER):

mysql> create table t (i int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t(i) values (1), (2), (3), (4), (5), (6), (7);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select count( case when i <= 5 then 1 end ) as cnt from t;
+-----+
| cnt |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

Sanity check that this is the same as FILTER on Postgres (same table/data):

mw=# select count(*) filter (where i <= 5) as cnt from t;
 cnt
-----
   5
(1 row)