Percentages of rows

Question

Let "total" the result of following query:

SELECT COUNT(DISTINCT C1) from T where <something>

("something" is a long condition over columns of T).

Let "partial1" the result of:

SELECT COUNT(DISTINCT C1) from T where <something> AND C2>10.

and "partial2" the result of:

SELECT COUNT(DISTINCT C1) from T where <something> AND C3>20.

there are a better way to write a query that returns the ratios "partial1/total" and "partial2/total"? If total is zero, any usual default value could be valid (null, -1, 1, 100, ...).

Answer 1

If you really need to have this done at MySql level, you could try something like :

SELECT
    partial1/total,
    partial2/total
FROM (
    SELECT 
        COUNT(DISTINCT C1) as total,
        COUNT(DISTINCT IF(C2>10, C1, NULL)) as partial1,
        COUNT(DISTINCT IF(C3>20, C1, NULL)) as partial2
    FROM T
    WHERE <something>
) as tmp
;