Multiplication and sum in MySql or php

Question

Multiplication and sum in MySql or php

Navigation

#1 by (2 votes)

2

I need to perform the following operation of the following table in mysql or php:

I need to calculate unit_value by quantity (unit_value * amount) and add all the values when the nonreceiver is equal to 11 (where no_recibo = 11).

I need to know how I can make this query in MySql or how I can do it in PHP to store it in a variable.

Greetings.

mysql php pdo

asked by ByGroxD 06.09.2017 в 04:50

source

1 answer

Show list of integers link an iframe with an HTML form

score 2 · Accepted Answer

Using MySQL

The query could be written using SUM() .

This is an aggregation function that:

Returns the sum of the indicated expression or column.
If the set of returns has no rows, SUM() returns NULL .
The keyword DISTINCT can be used to add only the values other than the expression or column.
If there are no matching rows, SUM() returns NULL .

The query would be written more or less like this:

SELECT 
    SUM(cantidad*valor_unitario) total 
    FROM calcular_20170906 
    WHERE no_recibo=11 
;

VIEW DEMO REXTESTER

CREATE TABLE IF NOT EXISTS calcular_20170906 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    no_recibo INT,
    cantidad INT,
    valor_unitario DECIMAL(12,2)
);

INSERT INTO calcular_20170906 (no_recibo, cantidad, valor_unitario)
    VALUES 
        (10,1,6000),
        (11,1,50000),
        (11,1,10000),
        (11,2,100000)
;

SELECT 
    SUM(cantidad*valor_unitario) total 
    FROM calcular_20170906 
    WHERE no_recibo=11 
;

`Resultado:`

total
260000,00

Using PHP

You would have to get all the rows, without using GROUP BY or group them by combining GROUP BY and GROUP_CONCAT and then go through the values and add them.

In my opinion it's not worth it, if you only need the total.