# Thread: Calculate percentage using inner join

1. Registered User
Join Date
Feb 2013
Posts
3

## 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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,112
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. Registered User
Join Date
Feb 2013
Posts
3

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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,112
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. Registered User
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. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,112
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
•