# Create a view that summarize vehilce owned from two tables cars and bikes

## All we need is an easy explanation of the problem, so here it is.

I have 3 tables owner, car and bikes where car and bike are dependent on owner.

owner(_name,_ssn)

I want to create a view that shows how many cars and bike I own
in the following way

vehicle_owned (_name, _ssn , vehicleamount)

where vehicle amount is car and bikes summarized.

I’ve tried using count with join on the tables but nothing seems to work

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

As far as you should not provide information enough about your table schema, I’ve improvised this one:

``````create table _owner(_name text, _ssn text);
create table _cars(_ownername text, _owner_ssn text, _model text);
create table _bikes(_ownername text, _owner_ssn text, _model text);
insert into _owner values
('me' ,'1'),
('you','2');
insert into _cars values
('me', '1','Ferrari Modena 360'),
('me', '1','Porsche Macan'),
('you','2','peugeot 205');
insert into _bikes values
('me', '1','Specialized Tarmac'),
('me', '1','Pinarello Dogma'),
('you','2','Scott Silence');
``````

You can use COUNT and GROUP BY to get your desired result:

``````select o._name, o._ssn,
((select count(*) from _bikes where _bikes._owner_ssn = o._ssn)
+
(select count(*) from _cars where _cars._owner_ssn = o._ssn)) as total_cars_and_bikes
from _owner o
;

+-------+------+----------------------+
| _name | _ssn | total_cars_and_bikes |
+-------+------+----------------------+
| me    | 1    | 4                    |
+-------+------+----------------------+
| you   | 2    | 2                    |
+-------+------+----------------------+
``````

Check it here: http://rextester.com/UZMF16252

### Method 2

Not tested, but something along these lines should work.

``````with
select _name, _ssn, coalesce(cc.cnt, 0) + coalesce(bc.cnt, 0) vehicleamount
from owner left join carcount cc on owner._ssn = cc._ownerSSN
left join bikecountcount cc on owner._ssn = cc._ownerSSN
``````

### Method 3

First, don’t use underscores in your names as initial characters. yuck.

``````create table _owner(_name text, _ssn text);
create table _cars(_ownername text, _owner_ssn text, _model text);
create table _bikes(_ownername text, _owner_ssn text, _model text);
insert into _owner values
('me' ,'1'),
('you','2');
insert into _cars values
('me', '1','Ferrari Modena 360'),
('me', '1','Porsche Macan'),
('you','2','peugeot 205');
insert into _bikes values
('me', '1','Specialized Tarmac'),
('me', '1','Pinarello Dogma'),
('you','2','Scott Silence');
``````

First you need all the `_cars` and `_bikes`. Because you don’t need any information about them, this will work.

``````SELECT _owner_ssn
FROM _cars
UNION ALL
SELECT _owner_ssn
FROM _bikes;
``````

Doesn’t get easier than that. Now if you don’t need the _owners which have 0 cars and 0 bikes you can just do

``````SELECT _owner_ssn, count(*) AS cars_and_bikes
FROM (
SELECT _owner_ssn
FROM _cars
UNION ALL
SELECT _owner_ssn
FROM _bikes
) AS t
GROUP BY _owner_ssn;

_owner_ssn | cars_and_bikes
------------+----------------
2          |              2
1          |              4
(2 rows)
``````

The rest is question ambiguity

1. Why are you storing the owner_name on the owner and the cars and the bikes? Shouldn’t it just be on the owner table?
2. What if two different cars and bikes have different owner names?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂