Adding value with Mysql

3

How do I add the values of the three selects below:

(
SELECT
    (valor1 + valor2 + valor3) AS total SELECT
        SUM(valor) AS valor1
    FROM
        pagamentos
    WHERE
        pagamentos.cliente = '1'
    AND contratosid = '9289'
    AND excluir != '1'
    UNION ALL
        SELECT
            SUM(valor) AS valor2
        FROM
            pagcielo
        WHERE
            pagcielo.confirmado = '1'
        AND contratosid = '9289'
        AND excluir != '1'
        UNION ALL
            SELECT
                SUM(valor) AS valor3
            FROM
                pagboleto
            WHERE
                pagboleto. STATUS = '1'
            AND contratosid = '9289'
            AND excluir != '1'
)
    
asked by anonymous 28.12.2016 / 14:58

2 answers

1

There is an error in the structure of your query.

First of all, choose to leave the queries connected by UNION with the same field nomenclature (it is not mandatory but facilitates):

SELECT
  SUM(valor) AS valor
FROM
  pagamentos
WHERE
  pagamentos.cliente = '1'
  AND contratosid = '9289'
  AND excluir != '1'

UNION ALL

SELECT
  SUM(valor) AS valor
FROM
  pagcielo
WHERE
  pagcielo.confirmado = '1'
  AND contratosid = '9289'
  AND excluir != '1'

UNION ALL

SELECT
  SUM(valor) AS valor
FROM
  pagboleto
WHERE
  pagboleto. STATUS = '1'
  AND contratosid = '9289'
  AND excluir != '1'

Subsequently treat the previous queries as subselect, adding the three fields. The final query would look like this:

SELECT 
  SUM(VALOR) AS TOTAL
FROM (
  SELECT
    SUM(valor) AS valor
  FROM
    pagamentos
  WHERE
    pagamentos.cliente = '1'
    AND contratosid = '9289'
    AND excluir != '1'

  UNION ALL

  SELECT
    SUM(valor) AS valor
  FROM
    pagcielo
  WHERE
    pagcielo.confirmado = '1'
    AND contratosid = '9289'
    AND excluir != '1'

  UNION ALL

  SELECT
    SUM(valor) AS valor
  FROM
    pagboleto
  WHERE
    pagboleto. STATUS = '1'
    AND contratosid = '9289'
    AND excluir != '1'
) T
    
28.12.2016 / 15:15
0

This way you can sum all SELECT together

select id, sum(valor) from (
    select id,valor from tabela_1 union all
    select id,valor from tabela_2 union all
    select id,valor from tabela_3
) x group by id
    
28.12.2016 / 15:12