Running Counts on Multiple Columns with One SQL Query

Question

I'm looking to run a count SQL query on multiple columns at once. 83 of them. For each, I'd simply like to figure out how many instances there are in which the value = 1.

ex.

select count(*) from [filename.filename] where [Column001] = 1

select count(*) from [filename.filename] where [Column002] = 1

All data in each column is marked with wither a 0 or a 1.

Instead of writing 83 small queries, is there a way for me to write it all in one query and have them all display as a table with the results?

show a sample of data and the expected result please

ScaisEdge
– ScaisEdge

2016-09-14 18:21:38 +00:00
Commented Sep 14, 2016 at 18:21 — ScaisEdge
– ScaisEdge, Commented Sep 14, 2016 at 18:21

Lamak · Accepted Answer · 2016-09-14 18:27:33Z

3

This seems to be what you want:

SELECT SUM(CASE WHEN Column_1 = 1 THEN 1 ELSE 0 END) N_1,
       SUM(CASE WHEN Column_2 = 1 THEN 1 ELSE 0 END) N_2,
       SUM(CASE WHEN Column_3 = 1 THEN 1 ELSE 0 END) N_3,
       .....
       SUM(CASE WHEN Column_83 = 1 THEN 1 ELSE 0 END) N_83
FROM YourTable;

edited Sep 14, 2016 at 18:27

answered Sep 14, 2016 at 18:22

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Running Counts on Multiple Columns with One SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related