xCloudx8 xCloudx8 - 4 months ago 24
MySQL Question

Division between samples in MySQL

It is possible to do a division in a sql query?

I'll give you an example:

I have:

Table_1 with "n" samples, and I can add rows so this number could change day by day.

and

Table_2 in which I do a count on my selected samples

Can I do:

#Samples_table_1 / #Samples_table_2 ?

(SELECT COUNT(X)
FROM TABLE_1
WHERE CONDITION;)
/
(SELECT COUNT(Y)
FROM TABLE_2
WHERE CONDITION;)

Answer

You need to enclose your above query (after removing the semicolon) by another SELECT.

SELECT 
IFNULL((
    SELECT 
    COUNT(X)
    FROM TABLE_1
    WHERE CONDITION
),0)
/
IFNULL((
    SELECT 
    COUNT(Y)
    FROM TABLE_2
    WHERE CONDITION
 ),1);

In addition to Gordon's answer:

If you expect fractional answer then use / operator like above.

And if you expect integer as answer then use DIV like below:

SELECT 
    IFNULL((
        SELECT 
        COUNT(X)
        FROM TABLE_1
        WHERE CONDITION
    ),0)
    DIV
    IFNULL((
        SELECT 
        COUNT(Y)
        FROM TABLE_2
        WHERE CONDITION
     ),1);