Home / Questions / 375728
Accepted
akko
Asked: 2020-07-24 09:51:11 +0800 CST 2020-07-24 09:51:11 +0800 CST

How to calculate total in MySQL?

  • 772

I have the following information in a MySQL table, what I need is to calculate the total by adding the result of multiplying the quantity by the corresponding price.

If it Precio con descuentois different from NULLit will be used to make the calculation.

| Cantidad   | Precio | Precio con descuento |
|------------|--------|----------------------|
| 1          | 10     | NULL                 |
| 2          | 10     | 5                    |
| 10         | 2      | 1                    |

1 * 10 = 10

2 * 5 = 10

10 * 1 = 10

TOTAL: 30

Currently I have:

SELECT cantidad, precio, precio_descuento FROM ventas WHERE cerrado = 1;
mysql

1 Answers

  1. Best Answer

    You can use the function coalesce()to multiply by one or the other.

    select   cantidad
       , precio
       , precio_descuento
       , cantidad * coalesce(precio_descuento, precio) total
  from ventas
 where cerrado = 1;

    The function coalesce()can take N parameters and returns the value of the first parameter that is not null. In this case, it will return the value of precio_descuentoif is not null, and if it is, the value of precio.

    Another possibility is to use a statement caseto determine which field to use:

    select   cantidad
       , precio
       , precio_descuento
       , cantidad * case 
                      when precio_descuento is null then precio 
                      else precio_descuento 
                    end total
  from ventas
 where cerrado = 1;

    Finally, you can use a function if()to achieve the same result

    select   cantidad
       , precio
       , precio_descuento
       , cantidad * if(precio_descuento is null, precio, precio_descuento) total
  from ventas
 where cerrado = 1;

    If you want the total of all sales, apply the aggregate function sum()on this calculated value, for example:

    select   sum(cantidad * coalesce(precio_descuento, precio)) TotalVentas
  from ventas
 where cerrado = 1;
    • 3