CODEWITHSUNDEEP
sqlpostgresqlselectsumsubquery
user2431581
user2431581

Reputation: 375

more than one row returned by a subquery used as an expression postgresql

postgresql, I have a table my_table 4 table that has columns stxstxid, stxuserid sumamountstx, userid, amountcredit, amountsrc My goal is to merge different rows that have the same stxstxid, into one row while summing the sales column of these selected rows into the merged row.

For example

stx                              stxitem    
stxid   stxuserid                stxid  amountstx
-------------------            -----------------------
001           A                 001            20
002           B                 002            12
002           B                 002            200
003           C                 003            360
003           C                 003            400
004           D                 004            300
004           D                 004            450
004           D                 004            100
005           E                 005            800
005           E                 005            950
005           E                 005            800
005           E                 005            600

srcitem         
srcid   userid  soueceid  amountsrc 
------------------------------------
A0001   src001    001       20
A0002   src002    002       212
A0003   src003    003       500
A0004   src004    004       800


credit      
creditID     stxid     amountcredit
---------------------------------------
9X0001        001         0
9X0002        002         0
9X0003        003        60
9X0004        004        50
9X0005        005        3150

This what I am supposed to get

result 

stxid   stxuserid   sumamountstx    userid  amountcredit    amountsrc
---------------------------------------------------------------------------
003           C           760        src003     60              500
005           E           3150                  3150

I did some research, and I found that the self join is supposed to do something similar to what I am supposed to get.

Upvotes: 0

Views: 2202

Answers (1)

Tomas Greif
Tomas Greif

Reputation: 22663

select
 a.stxid,
 a.stxuserid,
 x.sumamountstx,
 s.userid,
 s.amountsrc
from
 (select stxid, stxuserid from stx group by stxid, stxuserid) a
 join (select stxid, sum(amountstx) sumamountstx from stxitem group by stxid) x using (stxid)
 join credit using (stxid)
 left join srcitem s on (a.stxid = s.souceid)

Not sure this is correct. Give us sample data on sqlfiddle and we can test.

Upvotes: 1

Related Questions