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

4
  • 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

0
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
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?

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