Sum of values in tables and show the result

Question

Sum of values in tables and show the result

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a MySQL table, with Customer ID, Initial Due, Date and Subscription I need to make a query that adds all the values with the same id of the table and shows it in a new column, in the row of its related id.

Table:

CI | Deu_Inc | Fecha | Abonos
1 | 100.00 | 1-2-18 | 20
1 | 100.00 | 2-3-18 | 50
2 | 600.00 | 1-2-18 | 30
2 | 600.00 | 2-3-18 | 50
3 | 120.60 | 1-2-18 | 60
3 | 120.60 | 2-3-18 | 20

Desired result:

CI | Deu_Inc | suma | Total
1 | 100.00 | 70 | 30
2 | 600.00 | 80 | 520
3 | 120.60 | 80 | 40.60

mysql

asked by luis arce 02.09.2018 в 03:56

source

2 answers

Use min () and max () of an array with php Creation of dynamic table

score 1 · Answer 1

Following the original approach to the problem, I propose the following solution.

1.- Aggregation functions SUM()
2.- GROUP BY for grouping equal columns

SELECT CI AS id, DeudaInicial, SUM(Abonos) AS suma,
(DeudaInicial) - (SUM(Abonos)) AS Total
FROM data
GROUP BY id;
+------+--------------+------+----------+
| id   | DeudaInicial | suma | Total    |
+------+--------------+------+----------+
|    1 | 100.00       | 70   | 30.00    |
|    2 | 600.00       | 80   | 520.00   |
|    3 | 120.60       | 80   | 40.60    |
+------+--------------+------+----------+

If you notice, I do a GROUP BY() of the column CI to which I put an alias called id

Then the column DeudaInicial minus the column SUM(Abonos) remains to have just there the value of the sum made at the beginning   requested

The group by() I do so that the values of the column CI that have the alias id are grouped by coincidence and when there is   more than one do not show repeated

CLARIFICATION

Just consider that I use a table with the name data, you must put the name of the table you are using

score 0 · Answer 2

It would be;

select CI, Deuda, sum(Abonos) 'suma Abonos', Deuda - sum(Abonos) as total 
from  'deudas'
group by CI

Now, you have a problem in the initial table, you should separate the Debts from the Subscriptions, something like:

Deudas (CI, DeudaInicial)
Abonos (fk_CI, Fecha, Abono)

And in this case it would be:

select D.CI, D.Deuda, sum(A.Abono) 'suma Abonos', D.Deuda - sum(A.Abono) as total 
from  'Deudas' D
Inner Join Abonos A on D.CI = A.fk_CI
group by D.CI