SQL ORACLE - COUNT separately in multiple tables

Question

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

Just so we clear. You want to simply count number of rows in each table(looks like it to me from your question) or there is a more special requirement(give me number of users who favors subscription x, for example)? — Nick Krasnov
– Nick Krasnov, Commented Oct 26, 2016 at 8:28
Use three seperate select idUser, count(*)....from SUBSCRIPTION/HISTORY/FAVORITE in Derived Tables or CTEs and join them. — dnoeth
– dnoeth, Commented Oct 26, 2016 at 8:28
It would be helpful if you could describe your desired view on the data — ZeusNet
– ZeusNet, Commented Oct 26, 2016 at 8:30
Unrelated, but: you should really stop using those ancient, outdated and fragile implicit joins in the WHERE clause and start using an explicit JOIN operator — user330315
– user330315, Commented Oct 26, 2016 at 8:33
USER is a saved word how exactly are you referencing a table with that name without using qualifiers ("USER")? — David דודו Markovitz
– David דודו Markovitz, Commented Oct 26, 2016 at 10:10

rbr94 · Accepted Answer · 2016-10-26 08:31:33Z

4

Try this:

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

answered Oct 26, 2016 at 8:31

rbr94

2,2874 gold badges27 silver badges40 bronze badges

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

Comments

JohnHC · Accepted Answer · 2016-10-26 08:31:12Z

2

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

answered Oct 26, 2016 at 8:31

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

4 Comments

iAmoric Over a year ago

Thanks a lot, this is exactly what I want !

rbr94 Over a year ago

I think that's a very ugly approach using sub-selects in the LEFT JOIN, because you can easily approach this by using the sub-selects in the SELECT part.

rbr94 Over a year ago

@LucasPierrat Did you check my solution?

rbr94 Over a year ago

@LucasPierrat What do you mean?

David דודו Markovitz · Accepted Answer · 2016-10-26 11:13:45Z

1

P.s.

Except for great performance, when facing the lack of table such as "USER", this approach present a major advantage since there is no need to use FULL JOIN nor to generate a sub-query that will function as the base for LEFT JOIN

Solution 1

select      *  

from        (           select 'U' as tab ,idUser from "USER"
            union all   select 'S'        ,idUser from Subscription
            union all   select 'F'        ,idUser from Favourite
            union all   select 'H'        ,idUser from History
            )
            pivot (count(*) for tab in ('S' as nb_sub,'F' as nb_fav,'H' as nb_hist))            
;

Solution 2

This solution has less clean syntax but gives you a lot of freedom to manipulate the data ,e.g. add the nb_total column

select      idUser

           ,count (decode (tab,'U',1))  as nb_user  -- If the data is good there supposed to be 1 record per user
           ,count (decode (tab,'S',1))  as nb_sub
           ,count (decode (tab,'H',1))  as nb_fav
           ,count (decode (tab,'F',1))  as nb_hist    

           ,count (*)                   as nb_total        

from        (           select 'U' as tab ,idUser from "USER"
            union all   select 'S'        ,idUser from Subscription
            union all   select 'F'        ,idUser from Favourite
            union all   select 'H'        ,idUser from History
            )

group by    idUser
;

edited Oct 26, 2016 at 11:13

answered Oct 26, 2016 at 9:53

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

3 Comments

user5683823 Over a year ago

This answer gets my vote. OP should test the different answers, it is very likely this will be a significant improvement in performance over the join based solutions.

rbr94 Over a year ago

@mathguy But he just mark the first answer as accepted, which is probably the worst. I must admit, that this answer also is a good alternative instead of using sub-queries in the select part

user5683823 Over a year ago

@rbr94 - actually your CORRELATED subqueries are even worse than a join, since they will require going over each table many times (in a join each table is read only once). Happily, the Optimizer will likely re-write your solution with correlated subqueries as a multiple join query, resulting in the same plan as John HC's solution. Dudu's solution is entirely different and will (probably) result in a much better plan.

Collectives™ on Stack Overflow

SQL ORACLE - COUNT separately in multiple tables

3 Answers 3

Comments

4 Comments

Solution 1

Solution 2

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Solution 1

Solution 2

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related