CODEWITHSUNDEEP
sqloracle-database
Pahgo
Pahgo

Reputation: 114

Efficiently selecting a subset from a bigger selection in SQL

I have a monstrous query in Oracle SQL. My problem is that I need to take the % of two related queries.

So, what I'm doing is:

SELECT type*100/decode(total, 0, 1, total) as result
from (SELECT
(select count(*) from tb1, tb2, tb3
where tb1.fieldA = tb2.fieldB
and tb2.fieldC = tb3.fieldD
and tb3.fieldE = 'Some stuf') as type,
(select count(*) from tb1, tb2, tb3
where tb1.fieldA = tb2.fieldB
and tb2.fieldC = tb3.fieldD) as total from dual) auxTable;

As you can see my variable called type is a subset of the total variable. This is a simplified example of a much bigger problem..

Is there any efficient way of selecting the subset(type) from the total and then getting the percentage?

Upvotes: 1

Views: 66

Answers (1)

David Faber
David Faber

Reputation: 12485

Yes, there is a more efficient way of doing this:

SELECT type*100/DECODE(total, 0, 1, total) FROM (
    SELECT COUNT(*) AS total, SUM(DECODE(tb3.fieldE, 'Some stuf', 1, 0)) AS type
      FROM tb1, tb2, tb3
     WHERE tb1.fieldA = tb2.fieldB
       AND tb2.fieldC = tb3.fieldD
);

This SUM(DECODE(tb3.fieldE, 'Some stuf', 1, 0) will get a count of all records for which tb3.fieldE = 'Some stuff'. Alternately, you can use:

COUNT(CASE WHEN tb3.fieldE = 'Some stuff' THEN 1 END) AS type

It that CASE will return NULL when fieldE is not the chosen value, and NULLs are not counted in COUNT().

Upvotes: 4

Related Questions