Adding value with Mysql

Asked

Viewed 54 times

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'
)
mysql

2 answers

1


There is an error in the structure of your query.

First, choose to leave the queries connected by UNION with the same field nomenclature (it is not mandatory, but it 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'

Later treat the previous queries as subselect, adding up 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

0

This way you can add all the 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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.