Jump to content
dishadcruze

summing 2 columns data in single table with different condition

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

Share this post


Link to post
Share on other sites
SELECT 
    SUM(CASE WHEN type = 'poo' THEN qty ELSE 0 END) as order_qty
  , SUM(CASE WHEN type = 'por' THEN qty ELSE 0 END) as recd_qty
, ...

Share this post


Link to post
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

Share this post


Link to post
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

Edited by Barand

Share this post


Link to post
Share on other sites

Grouping by sku would also give the totals you expect but the values for po_no, date and vendor would be of no particular meaning as they could come from any arbitrary record in each of the groups.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • 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.