Adapt query to create VIEW which returns sum of the count of two Subqueries

Question

Adapt query to create VIEW which returns sum of the count of two Subqueries

Navigation

#1 by (5 votes)
#2 by (1 votes)
#3 by (0 votes)

5

From according to the documentation , MySQL does not allow the use of subqueries in FROM when the query is a view :

Subqueries can not be used in the FROM clause of a view.

That results 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 in tabela1 and 500 in tabela2 , query returns 1500.

With the following change to the query, it is already possible to create view , but the result is two lines, 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 query using it in a `view` ?

mysql

asked by anonymous 25.09.2014 / 19:25

3 answers

Creating a bar chart within another bar chart How to execute only one if without entering the other in PHP

score 5 · Answer 1

After some testing, here is 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 SELECT , still going to meet the desired one within the rules of MySQL.

score 1 · 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 view for view

CREATE VIEW mysql_precisa_de_gambiarras AS
SELECT Sum(total) FROM View_no_Mysql_que_nao_aceita_subquery

Finally I would stay:

SELECT * FROM mysql_precisa_de_gambiarras

score 0 · Answer 3

You can run the following SQL command:

Or you can add 1 field in table1, called countTabela1 and another in table2, called countTabela2 that will contain how many rows each table has. Then you do is create a table, called countTabela that has 2 fields:
1st field: a foreign key from table1, named FK_countTable1;
2nd field: a foreign key from table2, called FK_countTable2;
Only after completing the sum of the 2 fields:
SELECT FK_countTable1 + FK_countTable2 AS Total FROM countTab;

Adapt query to create VIEW which returns sum of the count of two Subqueries

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

3 answers

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