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
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
COUNT(*) - SUM(CASE WHEN name_thesame = true THEN 1 ELSE 0 END)). Unless I miss something obvious ?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
