2

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.

Improve this question

5 Answers 5

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@491243: You're right, I didn't. Fixed the error now. Thanks.
actually it will work if the column has identity set on it and you can count correctly, eg COUNT(DISTINCT x.identityColumn) :)
1

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
Improve this answer

Comments

0

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

Improve this answer

Comments

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

Comments

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.