SQL Select syntax workaround

Question

Let's say I have two existing tables, "dogs" and "cats":

 dog_name | owner
 ---------+------
 Sparky   | Bob
 Rover    | Bob
 Snoopy   | Chuck
 Odie     | Jon

 cat_name | owner
 ---------+------
 Garfield | Jon
 Muffy    | Sam
 Stupid   | Bob

How do I write a query with this output?

 owner | num_dogs | num_cats
 ------+----------+---------
 Bob   |     2    |    1
 Chuck |     1    |    0
 Sam   |     0    |    1
 Jon   |     1    |    1

The answer I've got is the next query

select owner, sum(num_dogs), sum(num_cats) from
  (select owner, 1 as num_dogs, 0 as num_cats from dogs
   union
   select owner, 0 as num_dogs, 1 as num_cats from cats)
group by owner

but the problem is that sql doesn't support "select * from (select...)" type of query

Instead, there has to be a table name after "from".

Unfortunately I can't afford myself to have temporary table or a table created with "select into" clause. I need some workaround for the proposed solution where a nested select clause is formed in this way.

What's your opinion?

Cheers

Answer 1

1. I'd say you need to add UNION ALL io just UNION to make this query work.

2. You need to name your inner select.

   SELECT * FROM (SELECT * FROM Table) MyInnerSelect

A better solution would be to create a table Owner and join the dog & cat tables.

Answer 2

My SQL does, just need to name the table... What database are you using?

 select owner, sum(num_dogs), sum(num_cats) from
  (select owner, 1 as num_dogs, 0 as num_cats from dogs
   union all
   select owner, 0 as num_dogs, 1 as num_cats from cats) as g
 group by owner

Answer 3

You need to alias the subquery:

select sq.owner, sum(sq.num_dogs), sum(sq.num_cats) from
    (select owner, 1 as num_dogs, 0 as num_cats from dogs
     union
     select owner, 0 as num_dogs, 1 as num_cats from cats
    ) as sq
group by sq.owner

Answer 4

"but the problem is that sql doesn't support "select * from (select...)" type of query"

Sure it does. you just need to give it a name like "select * from (select...)a"

Answer 5

You need to alias the subquery.

select owner, sum(num_dogs), sum(num_cats)
from (
    select owner, 1 as num_dogs, 0 as num_cats from dogs
    union all
    select owner, 0 as num_dogs, 1 as num_cats from cats
) a
group by owner

Notice the a immediately following the subquery.

Answer 6

create table cats (cat_name varchar(8), owner varchar(8))
create table dogs (dog_name varchar(8), owner varchar(8))
create table owners (owner varchar(8))

insert into owners values ('Jon')
insert into owners values ('Bob')
insert into owners values ('Chuck')
insert into owners values ('Sam')

insert into dogs values ('Sparky', 'Bob')
insert into dogs values ('Rover', 'Bob')
insert into dogs values ('Snoopy', 'Chuck')
insert into dogs values ('Odie', 'Jon')

insert into cats values ('Garfield', 'Jon')
insert into cats values ('Muffy', 'Sam')
insert into cats values ('Stupid', 'Bob')

select 
    owners.owner,
    count(distinct dog_name) as num_dogs,
    count(distinct cat_name) as num_cats
from 
    owners
        left outer join dogs on dogs.owner = owners.owner
        left outer join cats on cats.owner = owners.owner
group by owners.owner

Note that count(dog_name) should probably be count(dog_id)... multiple dogs can have same name different owners (heck... same name same owner is probably allowed).

Note the addition of DISTINCT to the count(..) to correct problem.

Answer 7

select
coalesce (d.owner, c.owner),
count(distinct d.dog_name) as num_dogs,
count(distinct c.cat_name) as num_cats
from dogs d
full join cats c on d.owner = c.owner
group by coalesce (d.owner, c.owner)

only because I don't like unions and sub selects for some reason... :)

Answer 8

Another take on this, more for highlighting different ways than necessarily performance.

In SQL Server -

SELECT 
ISNULL(c.owner, d.owner) AS owner, 
COUNT(d.dog_name) num_dogs, 
COUNT(c.cat_name) num_cats
FROM
dogs d
FULL OUTER JOIN 
cats c
ON 
d.owner = c.owner
GROUP BY
c.owner, d.owner
ORDER BY
ISNULL(c.owner, d.owner)

Answer 9

Here's another method, just for fun.

select o.owner, nd.numdogs, nc.numcats
from owners o
left join (select owner, count(dog_name) as numdogs
           from dogs
           group by owner) nd on nd.owner=o.owner
left join (select owner, count(cat_name) as numcats
           from cats
           group by owner) nc on nc.owner=o.owner

Answer 10

Can you create an owners table with all of the owner names? If so, the following query will give you the output you're after:

SELECT
     owners.owner,
     count(distinct dog_name),
     count(distinct cat_name)
FROM
     (
     Owners
     LEFT JOIN cats ON
          owners.owner = cats.owner
     )
LEFT JOIN dogs ON
     Owners.owner = dogs.owner
GROUP BY
     Owners.owner;