CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Americo
Americo

Reputation: 919

SQL -- Dividing two results

I have two querys below, both of which are feeding from the same "player" table. I want to divide query 1 by query 2 to get a relevant percentage. Im relatively new to more detailed SQL queries, as well as posting on forums...but please let me know if you have any suggestions on how to combine this to get the relevant percentage result.

1

Select
  sysdate,sum(Count(init_dtime))
From Player p
Where
  Trunc(Init_Dtime) > Trunc(Sysdate) - 7 
  And Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
  and trunc(create_dtime) < to_date('2015-sep-9','yyyy-mon-dd')
Group By Trunc(Init_Dtime)
Order By Trunc(Init_Dtime) Asc

2

Select
  Sum(Count(Create_Dtime))
From Player P
where 
  Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
  And Trunc(Create_Dtime) < To_Date('2015-sep-9','yyyy-mon-dd')
Group By Trunc(create_Dtime)
Order By Trunc(create_Dtime) Asc

Upvotes: 8

Views: 35350

Answers (1)

Nivas
Nivas

Reputation: 18344

You can just say

select sysdate,
       count((init_dtime)) / sum((Create_Dtime)) * 100 as percentage
  from Player p
 where Trunc(Init_Dtime) > Trunc(Sysdate) - 7 
   and Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
   and trunc(create_dtime) < to_date('2015-sep-9','yyyy-mon-dd')
   order by percentage asc

The group by in the SQLs are not needed as you are not really grouping by something. group by is useful when you need the percentage by player, for instance. Then you would say group by player_id and in the select would have the player_id:

select player_id, count(…)
  from …
 where …
group by player_id

EDIT: If the where clauses are different:

select sysdate, 
       (
           (select count((init_dtime))
             from player p
            where trunc(Init_Dtime) > trunc(Sysdate) - 7 
              and Trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
              and trunc(create_dtime) < to_date('2015-sep-9','yyyy-mon-dd'))
            / 
           (select count((Create_Dtime))
              from player P
             where trunc(Create_Dtime) >= To_Date('2012-mar-01','yyyy-mon-dd')
               and trunc(Create_Dtime) < To_Date('2015-sep-9','yyyy-mon-dd'))
       ) * 100 as percentage
from dual

Upvotes: 7

Related Questions