Lucas Pierrat Lucas Pierrat - 1 month ago 12
SQL Question

SQL ORACLE - COUNT separately in multiple tables

I have for tables : USER, SUBSCRIPTION, FAVORITE and HISTORY. I would like to count the number of subscription, the number of favorite and the number of history for each user

SELECT
U.idUser,
COUNT(S.idUser) AS nb_sub,
COUNT(F.idUser) AS nb_fav,
COUNT(H.idUser) AS nb_his
FROM
USER U,
SUBSCRIPTION S,
HISTORY H,
FAVORITE F
WHERE
U.idUser = S.idUser AND
U.idUser = F.idUser AND
U.idUser = H.idUser
GROUP BY
U.idUser;


I've tried this but it is not the result I want..

Thanks for helping me

Answer
select U.IDUser, 
       nb_sub,
       nb_fav,
       nb_his
from USER U
left join 
    (
    select idUser, count(*) as nb_sub from Subscription group by idUser
    ) S
  on U.idUser = S.idUser
left join 
    (
    select idUser, count(*) as nb_his from History group by idUser
    ) H
  on U.idUser = H.idUser
left join 
    (
    select idUser, count(*) as nb_fav from Favourite group by idUser
    ) F
  on U.idUser = F.idUser
Comments