Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Post Unanswered: Calculate percentage using inner join

    The following is the data structure
    the output expected is the percentage of all the user who has log time more than 5 for user selected in age group 70.

    userbasic
    user user age
    111 70
    112 80
    114 70
    113 50
    116 70


    userlog
    userid week logtime
    111 1 1
    112 1 1
    111 2 5
    111 3 6
    114 2 3
    114 3 3
    116 2 4
    116 3 8

    I tried to use the inner join however the results returned are all record from user log table matching criteria age 70, howerver to calculate percentage it is also needed that count of user
    who has log time > 5 from userlog table

    can anyone please provide some guidance as how to retreive percentage using optimized join

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, it would be easier to assist if you posted query you wrote. How is anyone capable of debugging code he/she can't see?

    Anyway: if your query returns records that match "criteria age 70" but "log time > 5" is missing, I'd say that the latter condition should be put into the WHERE clause as well. What do you think?

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Thanks for reply.

    Here is the query I tried.
    select ux.userid, ux.logtime, count(*) tot from userlog ux Inner join userbasic uo on uo.userid = ux.userid and uo.userage='70' group by ux.userid,ux.logtime;

    By providing logtime as > 5 it will only return user who has log time 5. however the expected result is the percentage of user who has log time > 5 and for this I understand firstly we need to get all the users who are in userage 70 and then derived the percentage who are in log time > 5.

    I am not sure how in an optimized way i can get the full set and derived the percetage from that.

    Help/guidance appreciated.

    Thanks.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Could you post a desired result based on input you provided? What would you like to get as an output? Which columns with which values? Talking about "percentage" - how do you calculate it?

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    The expected output is just one column i.e. calculated percentage of user who has log time > 5.

    In the above query based on the data provided there are 7 records returned for user with age > 70 however there are 2 user who has log time greater than 5 there from the above query i am able to get the total records however to calculate the percentage i understand what is needed is
    count of user log time > 5 / tot user * 100.

    from the above query i could get the tot user what is needed is further filter to get count of user with log time > 5. there from the above data the percentage is
    2* 100/7 = 29%.


    I am thinking there could be a way through inner join to filter further on the final count however not sure if this is correct and could not get it yet.

    Help/guidance appreciated.

    Thanks.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I see; here's one option:
    Code:
    SQL> select * from userbasic;
    
        USERID    USERAGE
    ---------- ----------
           111         70
           112         80
           114         70
           113         50
           116         70
    
    SQL> select * from userlog;
    
        USERID       WEEK    LOGTIME
    ---------- ---------- ----------
           111          1          1
           112          1          1
           111          2          5
           111          3          6
           114          2          3
           114          3          3
           116          2          4
           116          3          8
    
    8 rows selected.
    
    SQL> with
      2  t70log as
      3    (select l.userid, l.logtime
      4     from userlog l,
      5          userbasic b
      6     where b.userid = l.userid
      7       and b.userage = 70
      8    )
      9  select
     10    count(*) cnt_all,
     11    sum(case when logtime > 5 then 1 else 0 end) gt_5,
     12    --
     13    sum(case when logtime > 5 then 1 else 0 end) * 100 / count(*) result
     14  from t70log;
    
       CNT_ALL       GT_5     RESULT
    ---------- ---------- ----------
             7          2 28,5714286
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •