CODEWITHSUNDEEP
sqloracle-database
Dillon Tagle
Dillon Tagle

Reputation: 63

Dividing two simple select statements

I have two simple select statements and just want to divide the first one by the second one. 

SELECT COUNT (DISTINCT INITIATIVE_ID) 
FROM GPD_ERROR_WARNING_NEW

SELECT COUNT (DISTINCT SAVINGS_ID)
FROM GPD_SAVINGS_REGULAR

The first select statement results in 300 The second select statement results in about 1500, so just looking for that magic .2

I know it is simple to do by hand but looking to automate this and make it a view as it needs to be updated every hour.

Upvotes: 4

Views: 433

Answers (2)

daryosh setorg
daryosh setorg

Reputation: 126

You can write your query like this (in sql): 

SELECT
(SELECT DISTINCT COUNT(INITIATIVE_ID) FROM GPD_ERROR_WARNING_NEW) /
ISNULL((SELECT DISTINCT COUNT(SAVINGS_ID) FROM GPD_SAVINGS_REGULAR), 0)
FROM DUAL;

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26333

This will work, but it leaves you open to a "divide by zero" error:

SELECT
  (SELECT COUNT (DISTINCT INITIATIVE_ID) FROM GPD_ERROR_WARNING_NEW) /
  (SELECT COUNT (DISTINCT SAVINGS_ID) FROM GPD_SAVINGS_REGULAR)
FROM DUAL;

This will give you a null result instead of an error if the second (denominator) count comes up as zero:

SELECT
  (SELECT COUNT (DISTINCT INITIATIVE_ID) FROM GPD_ERROR_WARNING_NEW) /
  NULLIF((SELECT COUNT (DISTINCT SAVINGS_ID) FROM GPD_SAVINGS_REGULAR), 0)
FROM DUAL;

Upvotes: 6

Related Questions