Sum of values taking into account 3 fields of the table

Question

Sum of values taking into account 3 fields of the table

Navigation

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

1

I have the following table quotations that serves to fetch the totals of several budgets. And each budget has one piece ( part_id ) that can have several works:

+---------+-------------+----------+-----------------+
| part_id | alternative | revision | totals          |
+---------+-------------+----------+-----------------+
| 1       | 1           | 0        | 252.22          |
| 2       | 0           | 0        | 452             |
| 1       | 1           | 1        | 270             |
| 1       | 2           | 2        | 250             |
| 1       | 2           | 3        | 250             |
+---------+-------------+----------------------------+

In the table, I want to sum all the totals, but taking into account the following rules: - the part_id and alterantive is like a different budget - If there is more than one revision ( revision ), I'll get the last revision total.

That is, I initially need to stick with the following:

+---------+-------------+----------+-----------------+
| part_id | alternative | revision | totals          |
+---------+-------------+----------+-----------------+
| 2       | 0           | 0        | 452             |
| 1       | 1           | 1        | 270             |
| 1       | 2           | 3        | 250             |
+---------+-------------+----------------------------+

That is, in order to get the last revision for each alternative, I want to add the final total, which would be 972.

mysql sql

asked by anonymous 03.03.2017 / 18:23

2 answers

1

Hello, try this:

SELECT part_id, alternative, revision, totals
FROM (
SELECT tb1.part_id, tb1.alternative, tb1.revision, tb1.totals,
    ROW_NUMBER() OVER (PARTITION BY tb1.part_id ORDER BY tb1.revision DESC) AS RowNo
FROM orcamento tb1
) x
WHERE x.RowNo = 1

I hope I have helped.

03.03.2017 / 18:53

Scrolling through a file and splitting its contents into separate files using Python Writing IP addresses on file, each IP on a different line using Python

score 1 · Accepted Answer

I first ran the grouping to select only the largest revision and then the sum of these records:

select sum(totals) 
from quotations 
where (part_id, alternative, revision) in 
      (select part_id, alternative, max(revision) from quotations group by part_id, alternative)