How to order records in the query according to another ordination?

Asked

Viewed 1,596 times

8

I have a table with the following columns:

id - id_manager - value - source - id_source

When registering an "Account to Pay", insert in this table managerial breakdowns of the company. Assuming that it would need to pay to a given supplier the value of 600.00 divided into 3 installments of 200.00, and this value would be dismembered to the following managerial sectors: "HR", "ADMINISTRATIVE" and "WAREHOUSE". 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 will make the discharge of the first installment, I make a division of management for the movement of accounts, and insert in the same table the following values:

4 - 5 - 50,00 - M - 1

5 - 8 - 83,33 - M - 1

6 - 4 - 66,67 - M - 1

That is, both the breakdowns of the registration of the main document (Bill to Pay), as the discharge, They are in the same table.

When the second installment is paid, the apportionment values will be the same cited above, because the value of the installment is the same.

Now when I pay off the third (last) installment, I can not do the appropriate apportionment because if done, the total sum of the rates may be different from the dismembered amount in the main document (Bill to Pay). To solve this, I need to make the sum of all managerial discharges and subtract from the main. So to make the final discharge, it is necessary to make two consultations. The first query will bring me the values when registered, ordered by code would be something like:

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

This query returns to me:

5 - 150,00

8 - 250,00

4 - 200,00

To continue, I have a second consultation, which is the sum of the discharges grouped by 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 ordering, does not return in the same order as when registered the main document, returning something of the type:

4 - 133,34

8 - 166,66

5 - 100,00

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

I could have simplified the explanation more, but I was writing in my own way to better understand.

EDITED!

inserir a descrição da imagem aqui

sql postgresql

  • put a order by id at the end of the second select would not solve the sorting problem?

  • Not because I use an aggregation function, to work I would have to put the id in "Group By" too, but for what I need I cannot add, it has to be grouped only by "managerial".

  • Could you edit your question showing all the tables and their respective fields related to table dismemberment? And describe what this apportionment process would be? About order by, I’ve used it together with group by, and it’s really necessary to put the field in the grouping, but when I had to do this I didn’t have the query compromised.

  • I put a diagram to give a facilitated... the table that insert records for "Account to Pay" and "Account Movement" is the "dismemberment". The apportionment process is when I have an integral value and need to make a proportional division.

  • The apportionment would be to track the records of all plots of that particular account?

  • No, the apportionment is: ((parcel_value / account_pay_value) * dismemberment_value)). If someone is interested in helping, add me to Skype so I can better explain what I do and how I need to order a query. Skype: polyanomartini

  • Record the balance and the low value of each installment , if the balance is less than x cents zeroing the balance , a Rigger can do this.

Show 2 more comments

3 answers

1

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

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

0

Why don’t you just put it all in one session? I created something similar to your scenario and managed to get 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';

0

This query returns the total value and the sum of the installments paid 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 the id_origem be the same for all movements of that bill to pay. But I did not understand the id_origem since it does not appear in the scheme posted and I suspect that it has another meaning. If it is not the same will be necessary to include the id_conta_pagar in the desmembramento

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.