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

Answers


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

Need Your Help

ASP.NET for Windows Mobile Screen Size

c# asp.net windows-mobile

I'm developing a site that needs to display nicely on a Windows Mobile device. For some reason, a standard &lt;div&gt; on a page always scrolls out to view the entire page, despite the contents bei...

How to pass a JavaScript array into an inline function call generated by PHP

javascript php jquery arrays

I have build a dynamic list from an array and must print the onclick event inline, I don't know of anyway other way to do this.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.