0

table production

code part qty process_id
1 21 10 10
1 22 12 10
2 22 15 10
1 21 10 12
1 22 12 12

I have to extract data like based on process, every process has multiple part but I can't take every part's data, so that have to distinct on code for getting process wise summation of qty. how to get data like this in postgresql or in django

process_id qty
10 27
12 12

I tried in this way

Production.objects.values('process').distinct('code').annotate(total_qty=Sum('quantity'))
Noyon Islam
  • 23
  • 1
  • 5

1 Answers1

0

The following query gets your desired result, but from your snippet I'm not sure this is the logic you had in mind. If you add more detail I can refine the answer.

SELECT process_id, SUM(qty) qty
FROM production
WHERE part=22
GROUP BY process_id
Simon Notley
  • 2,070
  • 3
  • 12
  • 18
  • thanks for your reply but a minor issue, I can not define the part in the query that's why I am declaring distinct on code – Noyon Islam Nov 17 '22 at 03:37
  • I'm afraid I don't understand the logic you are after. I think that you want only one of the records for each unique combination of code and process_id, but which record do you want to keep? Always the one with the highest part number? – Simon Notley Nov 17 '22 at 07:33
  • You get my point but not with highest part number. I want record of process wise sum of qty using combination of distinct code_id and process_id - @Simon Notley – Noyon Islam Nov 19 '22 at 12:19
  • I still don't get what you are after. I can't see any way of purely grouping the data (as opposed to filtering some of it out like I did with `part=22`) that gets your example output. If you are just grouping (into non-overlapping groups) and summing the sum of your new qty column must be the same as the original one, and in your example it isn't. – Simon Notley Nov 19 '22 at 16:00