Getting Two Counts and Then Dividing Them

Getting two counts and then dividing them

Use SELECT A.NUM, A.DENOM, cast(A.NUM as float)/cast(A.DENOM as float)

SQL Server consider that A.NUM / A.DENOM are int, because A.NUM and A.DENUM are int

Trying to divide two counts in SQL

Oracle

SQL Fiddle: http://sqlfiddle.com/#!4/34298/8

Method 1:

SELECT 1 - (COUNT(DISTINCT DID) * 100 / COUNT(DISTINCT ID))
FROM TGAMAZING
cross join DIRECTORS;

Method 2:

SELECT 1 - 
    (
        (SELECT COUNT(DISTINCT DID) FROM TGAMAZING) * 100 /
        (SELECT COUNT(DISTINCT ID) FROM DIRECTORS)
    )
FROM DUAL;

SQL Server

SQL Fiddle: http://sqlfiddle.com/#!6/34298/3

Method 1

SELECT 1 - (COUNT(DISTINCT DID) * 100.0 / COUNT(DISTINCT ID))
FROM TGAMAZING
cross join DIRECTORS;

Method 2

SELECT 1 - 
    (
        (SELECT COUNT(DISTINCT DID) FROM TGAMAZING) * 100.0 /
        (SELECT COUNT(DISTINCT ID) FROM DIRECTORS)
    )

How to divide count from one table by the count from the other one or other solution needed

number of users who logged in more than once divided by all users within that day

You can do this with two levels of aggregation: first count the logins per user and day, then compute the ratio of counts greater than 1.

select session_date, avg((cnt > 1)::int) retention_rate
from (
    select date_trunc('day', session_time) session_date, count(*) cnt
    from retention_cohort
    group by session_date, device_id
) t
group by session_date

Getting a count of two different sets of rows in a table and then dividing them

This should work:

select
    (select count(*) from tasks where completed = 1) /
    (select count(*) from tasks where completed = 0)
from dual

Dividing two results of count with eachother in SQL

You could try following query:

SELECT COUNT(student.userid)::decimal / (SELECT COUNT(userid) FROM student) 
FROM borrowed 
CROSS JOIN student 
WHERE student.userid = borrowed.userid 
     AND current_date > expiredate AND returndate IS NULL;

Divide the results of two select queries

You may use conditional aggregation and simple division in one query:

select
  100.0 *
  count(
    case when ISOGEN_LINE_PROGRESS_ = 'C'
    then 1
    end
  )
  /
  nullif(count(
    case when PROJECT_NUMBER_ = 'PJ001234'
    then 1
    end
  ), 0) as rate_
FROM dbo.PID_Components_PROCESS_LINES
WHERE ISOGEN_LINE_PROGRESS_ = 'C'
  or PROJECT_NUMBER_ = 'PJ001234'

---

Related Topics