summing 2 columns data in single table with different condition

[dishadcruze]

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

[Barand]

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

  , ...

[dishadcruze]

@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

[Barand]

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 February 17, 2018 by Barand

[dishadcruze]

Now i can't change the method of storing data. how should i solve it then?

[Barand]

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.

[dishadcruze]

Yup!. Even i tried the same and this the only possible way to get the nearby result as i wanted. Thanks Barand