CODEWITHSUNDEEP
sqlcount
Paul Zelluf
Paul Zelluf

Reputation: 13

SQL count from 2 tables with true/false

I have 2 tables:

The RefNr in both tables is the unique identifier and European in Table1 is a True/False field.

My question is how do I do a count of all owners with an European car?

Upvotes: 0

Views: 89

Answers (3)

ChrisCarroll
ChrisCarroll

Reputation: 463

Not that it states which DBMS you are using, this should work in SSMS;

    SELECT  COUNT(*) AS EuroOwners
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.RefNr = t2.RefNr AND t1.European = 1

Upvotes: 0

Eray Balkanli
Eray Balkanli

Reputation: 7990

A solution for Sql Server follows:

Select count(t2.Owner) as EuropeanCarOwner
From Table1 t1
Inner join Table2 t2 on t2.RefNr = t1.RefNr
where t1.European = 1

Please note that is European column is not type of boolean but varchar, you can do t1.European = 'true' in the while clause

If a person can own multiple cars and each car is entered as a different record, each owner can be counted only one time if required via: count(distinct t2.owner)

Upvotes: 1

user330315
user330315

Reputation:

With the information given, I'd say you are looking for a join with a simple where condition? 

select count(distinct t2.owner)
from table1 t1
  join table2 t2 on t1.refnr = t2.refnr
where t1.european = true;

The distinct t2.owner is necessary in case someone owns more than one car.

Upvotes: 1

Related Questions