CODEWITHSUNDEEP
sqloracle-database
user1297532
user1297532

Reputation:

SQL: Use count from two different tables

How to construct a query for the following scenario:

I have three tables:

  1. People
  2. Books (linked with people on peopleserno)
  3. Videos (linked with people on peopleserno)

I want to create an SQL where the output gives one row containing how many books & videos a specific person has read/watched.

Example output:

John | 3 (books) | 2 (videos)

I have tried things like this, but it doesn't work:

select a.name, 
       count(b.serno) as books, 
       count(c.serno) as videos
  from people a, 
       books b, 
       videos c
 where a.serno = b.peopleserno
   and a.serno = c.peopleserno

Thank you.

Upvotes: 2

Views: 135

Answers (5)

Sunil Khatri
Sunil Khatri

Reputation: 46

select a.name, 
       count(b.title)||' (books)'  as books, 
       count(c.title)||' (videos)'  as videos 
from people a
left join books b on a.serno = b.peopleserno
left join videos c on a.serno = c.peopleserno
group by a.name


OR

select a.name, 
       ( select count(b.serno) from books b where a.serno = b.peopleserno ) as books, 
       ( select  count(c.serno) from videos c where a.serno = c.peopleserno) as videos 
from people a

Upvotes: 0

juergen d
juergen d

Reputation: 204894

You need a left join to get even users that did not read/watch anything and then you need to group by the user to get specific user counts

select a.name, 
       count(distinct b.title) as books, 
       count(distinct c.title) as videos 
from people a
left join books b on a.serno = b.peopleserno
left join videos c on a.serno = c.peopleserno
group by a.name

SQLFiddle demo

Upvotes: 3

John Woo
John Woo

Reputation: 263883

It is safe to do the calculation in a subquery unless the table has auto_incremented column present on it. Assuming that the table has no auto_incremented column, 

SELECT  a.Name,
        COALESCE(b.TotalBook, 0) TotalBook,
        COALESCE(c.TotalVideo, 0) TotalVideo
FROM    People a
        LEFT JOIN
        (
            SELECT  peopleserno, COUNT(*) TotalBook
            FROM    Books
            GROUP   BY peopleserno
        ) b ON a.serno = b.peopleserno
        LEFT JOIN
        (
            SELECT  peopleserno, COUNT(*) TotalVideo
            FROM    Videos
            GROUP   BY peopleserno
        ) c ON a.serno = c.peopleserno

Upvotes: 1

Robert
Robert

Reputation: 25763

select a.name, 
       ( select count(b.serno) from books b where a.serno = b.peopleserno ) as books, 
       ( select  count(c.serno) from videos c where a.serno = c.peopleserno) as videos 
from people a

Upvotes: 0

Miguel Prz
Miguel Prz

Reputation: 13792

you have to use a GROUP BY clause by the fields you need to agregate to count.

Upvotes: 0

Related Questions