How to sort records in the query according to another sort?

Question

How to sort records in the query according to another sort?

Navigation

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

8

I have a table with the following columns:

id - manager_id - value - source - source_id

By registering an "Account to Pay", I enter in this table the company's management breakdowns. Assuming that it would have to pay a certain vendor the value of 600.00 divided into 3 installments of 200.00, and that amount be broken down into the following managerial sectors: "RH", "ADMINISTRATIVE" and "ALMOX". My table would have the following values:

1 - 5 - 150.00 - P - 1

2 - 8 - 250.00 - P - 1

3 - 4 - 200.00 - P - 1

When I am going to discharge the first installment, I make an apportionment of the managers for the account movement, and I insert the following values in the same table:

4 - 5 - 50.00 - M - 1

5 - 8 - 83.33 - M - 1

6 - 4 - 66.67 - M-1

In other words, both the dismemberment of the main document (Payable) and the discharge document are in the same table.

When you remove the second installment, the apportionment values will be the same as previously mentioned, because the installment value is the same.

Now when I am removing the third (last) installment, I can not make the proper apportionment because if it does, the total sum of the apportionments may be different from the amount dismembered in the main document (Payable). To solve this, I need to make the sum of all managers removed and subtract from the principal. So to make the last discharge, you need to make two inquiries. The first query will bring me the values when registered, sorted by code would look something like:

SELECT
 id_gerencial,
 valor
FROM
 desmembramento
WHERE
 origem = 'P'
ORDER BY
 id;

This query returns me:

5 - 150.00

8 - 250.00

4 - 200.00

To continue, I have a second query, which is the sum of the grouping by the management accounts:

SELECT
 id_gerencial,
 SUM(valor) AS soma
FROM
 desmembramento
WHERE
 origem = 'M'
GROUP BY
 id_gerencial;

It turns out that this query for not having an order does not return in the same order as when registering the main document, returning something like:

4 - 133.34

8 - 166.66

5 - 100.00

How do I make the second query come in the same order as the managers when they were registered?

I could have simplified the explanation further, but I've been writing it my way to understand it better.

EDITED!

sql postgresql

asked by anonymous 10.12.2015 / 20:05

3 answers

QT + OpenCV on Mac OS 10.10.3 com error: symbol (s) not found for architecture x86_64 How to resolve the java.lang.NoClassDefFoundError error when running rhinit () on R

score 1 · Answer 1

If order by does not work with group by, this should work:

SELECT * FROM (
  SELECT
   id_gerencial,
   SUM(valor) AS soma
  FROM
   desmembramento
  WHERE
   origem = 'M'
  GROUP BY
   id_gerencial
) ORDER BY id_genrencial;

score 0 · Answer 2

Why do not you put everything into one query? I created something similar to your scenario and managed to get to the result using:

select
    d1.id_gerencial,
    d1.valor as valor_a_ratear,
    (select sum(d2.valor) from desmembramento as d2 where d2.id_origem = d1.id_origem and d2.id_gerencial = d1.id_gerencial and d2.origem = 'M') as valor_quitado,
    d1.valor - (select sum(d2.valor) from desmembramento as d2 where d2.id_origem = d1.id_origem and d2.id_gerencial = d1.id_gerencial and d2.origem = 'M') as valor_aberto
from
    desmembramento as d1
where
    d1.origem = 'P';

score 0 · Answer 3

This query returns the total value and sum of the paid installments for each id_gerencial :

select id_gerencial, dp.valor as valor_P, sum(dm.valor) as valor_M
from
    desmembramento dp
    inner join
    desmembramento dm using (id_gerencial, id_origem)
where dp.origem = 'P' and dm.origem = 'M'
group by 1, 2
order by 1

But it depends on id_origem being the same for all movements of that account payable. But I did not understand id_origem since it does not appear in the posted schema and I suspect it has another meaning. If it is not the same it will be necessary to include id_conta_pagar in desmembramento