SQL To count values of multiple columns

Question

I have a table, with columns like this:

name1,name2,name_thesame,adress1,adress2,adress_thesame,city1,city2,city_thesame

In all columns ending with _thesame, there is a true or false depending if name1 and name2 are the same, same with adress etc etc.

I now need a query that returns a count how many true and false i got for each of the _thesame columns.

Cant wrap my head around how to do this query - any body got some ideas or pointers? Thanks

Edit your question and provide sample data and desired results. The question is not entirely clear. — Gordon Linoff, Oct 06 '15 at 11:31
http://stackoverflow.com/questions/17194145/sql-count-based-on-column-value is practically the same question — , Oct 06 '15 at 11:32
@user5051310 The linked question counts a single column, while this counts multiple columns. — Stevoisiak, Apr 20 '22 at 18:07

score 2 · Accepted Answer · answered Oct 06 '15 at 11:34

2

For a single property you can do:

select name_thesame, count(*) 
from table
group by name_thesame

This will give you results like:

 true     10
 false    15

If you want to have it as a list for multiple columns, you can just union the queries:

select 'Name', name_thesame, count(*) 
  from table
  group by name_thesame
union 
select 'Address', adress_thesame, count(*) 
  from table
  group by adress_thesame

getting:

 Name    true   10
 Name    false  15
 Address true   20
 Address false  5

answered Oct 06 '15 at 11:34

SWeko

30,434
10
71
106

Thanks, yeah that would give me the correct results. However I was hoping for a solution to do it programmaticly, so I dont have to hard code every column name, as there are 100+ where I want the counts to be done. But I will accept the answer and do the hard coding, thanks – user2199192 Oct 06 '15 at 11:40
2

To be honest, 100+ (meaning 300+ field in the table) is a bit of a red flag, design wise :) – SWeko Oct 06 '15 at 11:43
@user2199192, specify a database platform, I'll try to draft a script that will write the query for you. – SWeko Oct 06 '15 at 11:45

xlecoustillier · Answer 2 · 2015-10-06T11:55:00.140

2

This is another option:

SELECT SUM(CASE WHEN name_thesame = true THEN 1 ELSE 0 END) as nametrue,
       SUM(CASE WHEN name_thesame = false THEN 1 ELSE 0 END) as namefalse,
       SUM(CASE WHEN adress_thesame = true THEN 1 ELSE 0 END) as adresstrue,
       SUM(CASE WHEN adress_thesame = false THEN 1 ELSE 0 END) as adressfalse,
       SUM(CASE WHEN city_thesame = true THEN 1 ELSE 0 END) as citytrue,
       SUM(CASE WHEN city_thesame = false THEN 1 ELSE 0 END) as cityfalse
FROM yourTable

You can tweak it to deal with NULLs as well if relevant:

...
CASE WHEN name_thesame = false OR name_thesame IS NULL THEN 1 ELSE 0 END
...

or either NVL(), ISNULL(), IFNULL() or COALESCE(), depending on the DBMS you're using (syntax is always the same):

...
CASE WHEN COALESCE(name_thesame, false) = false THEN 1 ELSE 0 END
...

Result:

nametrue | namefalse | adresstrue | adressfalse | citytrue | cityfalse
       5 |         7 |          2 |           1 |       10 |         8

edited Oct 06 '15 at 11:55

answered Oct 06 '15 at 11:37

xlecoustillier

16,183
14
60
85

If he doesn't have `NULL` can he just subtract # of true from the total # of rows to get # of false? – Oct 06 '15 at 11:48
Of course it can be done, but it wouldn't make the query shorter or more performant (something like `COUNT(*) - SUM(CASE WHEN name_thesame = true THEN 1 ELSE 0 END)`). Unless I miss something obvious ? – xlecoustillier Oct 06 '15 at 11:50
Could put it in the application but I thinking about it, no idea if that's in any way better. – Oct 06 '15 at 11:54

SQL To count values of multiple columns

2 Answers2