CODEWITHSUNDEEP
sqloracle-databaseaggregate-functions
HJW
HJW

Reputation: 23443

How to select count as a percentage over the total in Oracle using any Oracle function?

I have an SQL statement that counts over the total number of rows active packages whose end date is null. I am currently doing this using (x/y) * 100:

SELECT (SELECT COUNT(*) 
          FROM packages 
         WHERE end_dt IS NULL) / (SELECT COUNT(*) 
                                    FROM packages) * 100 
  FROM DUAL;

I wonder if there is a way to make use of any Oracle function to express this more easily?

Upvotes: 3

Views: 15589

Answers (3)

RooZA
RooZA

Reputation: 31

The original post is a little long in the tooth but this should work, using the function "ratio_to_report" that's been available since Oracle 8i:

SELECT
  NVL2(END_DT, 'NOT NULL', 'NULL') END_DT,
  RATIO_TO_REPORT(COUNT(*)) OVER () AS PCT_TOTAL
FROM
  PACKAGES
GROUP BY
  NVL2(END_DT, 'NOT NULL', 'NULL');

Upvotes: 3

Andriy M
Andriy M

Reputation: 77687

So, basically the formula is

COUNT(NULL-valued "end_dt") / COUNT(*) * 100

Now, COUNT(NULL-valued "end_dt") is syntactically wrong, but it can be represented as COUNT(*) - COUNT(end_dt). So, the formula can be like this:

(COUNT(*) - COUNT(end_dt)) / COUNT(*) * 100

If we just simplify it a little, we'll get this:

SELECT (1 - COUNT(end_dt) * 1.0 / COUNT(*)) * 100 AS Percent
FROM packages

The * 1.0 bit converts the integer result of COUNT to a non-integer value so make the division non-integer too.

The above sentence and the corresponding part of the script turned out to be complete rubbish. Unlike some other database servers, Oracle does not perform integer division, even if both operands are integers. This doc page contains no hint of such behaviour of the division operator.

Upvotes: 6

OMG Ponies
OMG Ponies

Reputation: 332631

There's no functionality I'm aware of, but you could simply the query to be:

SELECT SUM(CASE WHEN p.end_dt IS NULL THEN 1 ELSE 0 END) / COUNT(*) * 100
  FROM PACKAGES p

Upvotes: 9

Related Questions