SQL Oracle - Multiple count queries on multiple tables

Question

Maybe for some people it might look very simple, but I just cant get it.

My tables are:

CREATE TABLE USERS (user_ID number PRIMARY KEY, username varchar2(32), password varchar2(32));
CREATE TABLE VIDEOS (video_ID number PRIMARY KEY, title varchar(64), description varchar(128));
CREATE TABLE VIEWS (view_ID number PRIMARY KEY, user_ID number, video_ID number);
CREATE TABLE FAVORITES (fav_ID number PRIMARY KEY, user_ID number, video_ID number);

I ve created those separated queries:

SELECT u.username AS "Username", count(*) AS "Views"
FROM Views v, Videos vd, Users u
WHERE v.user_id = u.user_id
AND v.video_id = vd.video_id
GROUP BY u.username

SELECT u.username AS "Username", count(*) AS "Favorites"
FROM Favorites f, Videos vd, Users u
WHERE f.user_id = u.user_id
AND f.video_id = vd.video_id
GROUP BY u.username

And I want a query to show something like that in only one simple query:

Username    Views      Favorites
-------------------------------
Person1      12          1
Person2      234         21
...

I Googled bunch of similar questions but I couldnt make any of them to work. So any help is greatly appreciated.

JOIN. JOIN. JOIN. I also removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff
– Gordon Linoff, Commented Oct 27, 2020 at 12:13

George Joseph · Accepted Answer · 2020-10-27 12:55:06Z

You are progressing on the right track. -> You got two queries and you wish to see them together. You could perform a full outer join to get your results you are looking for as below.

with fave
  as (
SELECT u.username AS "Username"
      , count(*) AS "Favorites"
  FROM Favorites f
  JOIN Videos vd
    ON f.video_id = vd.video_id
  JOIN Users u
    ON f.user_id = u.user_id
 GROUP BY u.username
      )
,views
    as (SELECT u.username AS "Username"
             , count(*) AS "Views"
         FROM Views v
         JOIN Videos vd
           ON v.video_id = vd.video_id
         JOIN Users u
           ON v.user_id = u.user_id
        GROUP BY u.username
       )
    select isnull(f.username,v.username) as username
           ,f.favourites
           ,v.views
      from fave f
full outer join views v
        on f.username=v.username

Since you know your data better, you could optimize the query further. Eg: it could be a rule that user who has set a favourite would also have viewed the video. If this is true then you can write a better query to optimize the dataset in a single block, instead of two blocks using full outer join

forpas · Accepted Answer · 2020-10-27 12:56:13Z

Aggregate separately in Views:

select user_id, count(*) counter
from Views
group by user_id

and Favorites

select user_id, count(*) counter
from Favorites
group by user_id

and finally LEFT join Users to the above queries:

select u.username, 
       coalesce(v.counter, 0) Views, 
       coalesce(f.counter, 0) Favorites
from users u
left join (
  select user_id, count(*) counter
  from Views
  group by user_id 
) v on v.user_id = u.user_id
left join (
  select user_id, count(*) counter
  from Favorites
  group by user_id
) f on f.user_id = u.user_id

I used LEFT joins because there may exist users that did not see any video or do not have any favorites. In any of these cases COALESCE() will return 0 instead of null.

The table Videos is not needed.

Collectives™ on Stack Overflow

SQL Oracle - Multiple count queries on multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related