Search code examples
mysqlcountnosqlaverageworkbench

Divide count by average in SQL from three different tables

I have three tables and I am using counts from each tables to get a rate%. This is the formula and I am trying to replicate it using MySQL

Count1.t1/AVERAGE(Count2.t2,Count3t.3)

So far the average is working and this is what I have: This code block gives me the average between t1 and t2.

SELECT AVG(ID.TAvg) FROM ( select count(*) as TAvg from t2 UNION Select count(*) as TAvg from t3) ID;

However, if I divide t1 count with the average it breaks.

(SELECT COUNT(ID) from t1) / (SELECT AVG(ID.TAvg) FROM ( select count(*) as TAvg from t2 UNION Select count(*) as TAvg from t3) ID);

Can anyone please help with this? I am basically trying to get a count from one table and divide it with an average from two separate tables. I appreciate the help.

Solution

  • Your:

    (SELECT COUNT(ID) from t1) / (SELECT  AVG(ID.TAvg) FROM ( select count(*) as TAvg from t2 UNION Select count(*) as TAvg from t3) ID)

    is the value you want, but it is an expression, not a select. You can just add a select:

    SELECT (SELECT COUNT(ID) from t1) / (SELECT  AVG(ID.TAvg) FROM ( select count(*) as TAvg from t2 UNION Select count(*) as TAvg from t3) ID)

    or do the divide in the from t1:

    SELECT COUNT(ID) / (SELECT  AVG(ID.TAvg) FROM ( select count(*) as TAvg from t2 UNION Select count(*) as TAvg from t3) ID) from t1

    (Though you don't say what "it breaks" actually means; do you in fact have an ID column in t1 and want to count rows where ID is not null? If you just want to count all rows in t1, do count(*) or count(1), not count(ID).)

    One other thing: UNION means UNION DISTINCT, which removes duplicate rows after unioning (as opposed to UNION ALL which just unions the rows from the two queries). Since you are just averaging two tables, there is no problem, but if you were summing, or averaging three tables, there would be. For instance, if you were averaging 3, 3, and 6, with UNION ALL, that would produce 4, but with UNION DISTINCT, it first becomes just 3 and 6, producing an average of 4.5. You almost always want to say UNION ALL, not just UNION.