CODEWITHSUNDEEP
sqlpostgresql
JCC96
JCC96

Reputation: 31

Group together duplicates in Postgresql

I am making a query to see how much stock gets booked into our warehouse, how do I group together all the duplicates so all the quantities can be as one and the same product isn't showing multiple times? I will post a screenshot to show what I get and the code I'm using. Thanks

Duplicates example 

select b.qty, a.part, a.desc1, a.main_supplier as supplier_acc_no, c.name as 
 supplier_name, a.main_mpn, a.date_receipt as date_booked_in
from public.stock as a
inner JOIN public.sthist AS b ON a.part = b.part
inner join public.supplier as c on a.main_supplier = c.account
where a.date_receipt > date_trunc('day', CURRENT_DATE) - INTERVAL '2 days'
and b.tran_type = 'POGRN'

Upvotes: 1

Views: 53

Answers (1)

L. Scott Johnson
L. Scott Johnson

Reputation: 4382

sum and group by (and appropriate functions for any non-group-by expressions). e.g.,

select sum(b.qty), a.part, min(a.desc1), 
min(a.main_supplier) as supplier_acc_no, min(c.name) as supplier_name, 
min(a.main_mpn), min(a.date_receipt) as date_booked_in
from public.stock as a
inner JOIN public.sthist AS b ON a.part = b.part
inner join public.supplier as c on a.main_supplier = c.account
where a.date_receipt > date_trunc('day', CURRENT_DATE) - INTERVAL '2 days'
and b.tran_type = 'POGRN'
group by a.part

Upvotes: 2

Related Questions