Adapt query to create VIEW that returns sum of count of two Subqueries

Question

Of agreement with the documentation, Mysql does not allow the use of Subqueries in the FROM when the consultation is a view:

Subqueries cannot be used in the FROM clause of a view.

Resulting in the following error:

1349: View’s SELECT contains a subquery in the FROM clause

The following query returns the sum of the count of the records of each table:

SELECT SUM(total)
FROM (
    SELECT COUNT(*) AS total
    FROM tabela1
    WHERE escondido='não'

    UNION ALL

    SELECT COUNT(*) AS total
    FROM tabela2
    WHERE escondido='não'
) t

Assuming 1000 records on tabela1 and 500 in tabela2, query returns 1500.

---

With the following amendment to the consultation, it is already possible to create the view, but the result is two rows, each with the total records of each table:

SELECT COUNT(*) AS total
FROM tabela1
WHERE escondido='não'

UNION ALL

SELECT COUNT(*) AS total
FROM tabela2
WHERE escondido='não'

How to keep the original concept of the consultation using it in a view ?

Answer 1

After a few tests, here’s a way to resolve the issue:

SELECT (
    (SELECT COUNT(*) AS total
     FROM tabela1
     WHERE escondido='não')

    +

    (SELECT COUNT(*) AS total
     FROM tabela2
     WHERE escondido='não')
) AS total

I basically passed the logic applied in FROM to the SELECT still going to meet the intended within the rules of Mysql.

Answer 2

Two other alternatives would be:

• Use a Sum() out of view:

  SELECT Sum(total) FROM View_no_Mysql_que_nao_aceita_subquery
  

• Create a view to the view

  CREATE VIEW mysql_precisa_de_gambiarras AS
  SELECT Sum(total) FROM View_no_Mysql_que_nao_aceita_subquery
  

  At last it would be:

  SELECT * FROM mysql_precisa_de_gambiarras
  

Answer 3

You can run the following SQL command:

Or you can add 1 field in Tabela1, called countTabela1 and another in table2, called countTabela2 that will contain how many rows each table has.
Then you create a table, called countTable that has 2 fields:
1st field: a foreign key of Tabela1, called Fk_counttabela1;
2nd field: a foreign key in table 2, called Fk_counttabela2;
Only then do the sum of the 2 fields:
SELECT Fk_counttabela1 + Fk_counttabela2 AS Total FROM countTable;