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

Navigation
3

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
    source

    1 answer

    4

    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
    source
    Know if a word is found in a string in php Error Crud update Laravel