CODEWITHSUNDEEP
mysqlsqldivision
xCloudx8
xCloudx8

Reputation: 721

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;)

Upvotes: 1

Views: 46

Answers (2)

1000111
1000111

Reputation: 13519

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);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can just put a select in front:

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

You also need to remove the semicolons.

If you are concerned about divide by zero, I would move the expressions to the from and phrase it as:

SELECT (x.cnt / NULLIF(y.cnt, 0))
FROM (SELECT COUNT(X) as cnt FROM TABLE_1 WHERE CONDITION) x CROSS JOIN
     (SELECT COUNT(Y) as cnt FROM TABLE_2 WHERE CONDITION) as y

Upvotes: 2

Related Questions