Apply a WHERE to my SQL statement in a selective and total SUM


guys I have the following conflict:

I have the following SQL statement

SELECT SUM(monto) AS 'total',
    SUM(CASE WHEN status_pago = 'PENDIENTE' THEN 1 ELSE 0 END) AS 'pendiente',
      SUM(CASE WHEN status_pago = 'APROBADO' THEN 1 ELSE 0 END) AS 'aprobado'
    FROM pagos

It gives me the following result:

  • The total of the sum of all amounts in the amount column
  • The number of rows with pending status
  • and the number of rows with approved status
  • and I would like to have additionally a value of the sum of the amounts where the status is equal to approved and logically in the sentence I have I can not place that condition with a WHERE because it would alter the result of number of rows with status pending.

    asked by Jose M Herrera V 10.10.2018 в 04:43

    1 answer


    You only have to make a small change in your query.

    You already have it solved, but instead of adding 1 or 0, add the amount or zero ...

    something like this:

    SUM(CASE WHEN status_pago = 'APROBADO' THEN monto ELSE 0 END) AS 'monto_aprobado'
    answered by 10.10.2018 / 04:49