0

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

  • 1
    what means ssn? – McNets Mar 1 '17 at 22:44
  • 5
    edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots – a_horse_with_no_name Mar 1 '17 at 22:48
  • 1
    do you expect the whole view script or only the select command? – McNets Mar 1 '17 at 23:05
  • 1
    I have answered this question to the best of my ability but I regret working on it. You need more information see the questions in my answer. – Evan Carroll Mar 2 '17 at 19:59
2

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

|improve this answer|||||
0

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
|improve this answer|||||
0

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?
|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.