Jump to content

summing 2 columns data in single table with different condition


dishadcruze

Recommended Posts

I have 2 tbles journal_main and journal_item.

 

in journal main i have fields like

id  | po_no  | period | post_date  | vendor 
 1  | PO123  | 12     | 2018-02-12 | XYZ
 2  | PO234  | 12     | 2018-02-13 | ABC

in journal_item

id | ref_id | type | sku   | qty  
1  | 1      | poo  | A123  | 12  
2  | 1      | poo  | B234  | 20   
3  | 2      | por  | A123  | 2    
4  | 2      | por  | A123  | 3    
5  | 2      | por  | B234  | 6   

i want to get the output as

po_no  | date       | vendor | sku   | ordered_qty  | received_qty | balance 
po123  | 2018-02-12 | XYZ    | A123  | 12           | 5            | 7
po123  | 2018-02-12 | ABC    | B234  | 20           | 6            | 14

i am not getting how to combine 2 queries in a single query. Here i have 2 queries which gives me ordered_qty and received_qty

separately

 

for ordered_qty

select journal_main.id, journal_main.po_no, journal_main.post_date, journal_main.vendor,  journal_item. sku,  SUM(journal_item.qty)  AS Oqty FROM journal_main INNER JOIN journal_item ON journal_main.id=journal_item.ref_id WHERE journal_item.type='poo' GROUP BY journal_item.sku, journal_main.id

for received_qty

select journal_main.id, journal_main.po_no, journal_main.post_date, journal_main.vendor,  journal_item. sku,  SUM(journal_item.qty)  AS Rqty FROM journal_main INNER JOIN journal_item ON journal_main.id=journal_item.ref_id WHERE journal_item.type='por' GROUP BY journal_item.sku, journal_main.id
Link to comment
Share on other sites

@Barand:

i tried like this

select jm.po_no, jm.vendor, jm.post_date, ji.ref_id, 
sum(case when ji.type = 'poo' then qty else 0 end) as ordered, 
sum(case when ji.type = 'por' then qty else 0 end) as received, 
sum(case when ji.type = 'poo' then qty else - qty end) as total 
FROM journal_main jm 
INNER JOIN journal_item ji 
ON jm.id=ji.ref_id 
WHERE ji.type IN('poo', 'por') group by jm.id

but it returns ordered and received in separate row

Link to comment
Share on other sites

Of course it does. Look at the data you posted.

 

for refid=1, all types are 'poo'

for refid=2. all types are 'por'

 

If your journal_item data were

 

+----+--------+------+------+------+
| id | ref_id | type | sku  | qty  |
+----+--------+------+------+------+
|  1 |      1 | poo  | A123 |   12 |
|  2 |      2 | poo  | B234 |   20 |
|  3 |      1 | por  | A123 |    2 |
|  4 |      1 | por  | A123 |    3 |
|  5 |      2 | por  | B234 |    6 |
+----+--------+------+------+------+

 

then you would get the result you expect

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.