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)

cars(_ownername,_ownerSSN, model)

bikes(_ownername,_ownerSSN, model)

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 
carcount as (select count(*) cnt, _ownerSSN from cars group by _ownerSSN),
bikecount as (select count(*) cnt, _ownerSSN from bikes group by _ownerSSN),
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.

You’re not thinking about this problem right. It’s actually simpler than that…

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply