How to get total qty and consumed qty into single results using SQL query

Question

I am having below Tables

1. Material Unit:

id | Unit_name
1  | Nos.
2  | lts

2. Material Table:

id | Material_name
1  | bricks
2  | Cement

3. Grn Table:

id | material_id | qty | unit
1  | 1           | 100 |   1
2  | 2           | 500 |   1
3  | 2           | 100 |   1
4  | 1           | 200 |   1

4. Consumption table:

id | material_id | qty | unit
1  | 1           | 50  |   1
2  | 2           | 100 |   1

Results expected is as below:

Material Name | Unit | Total Qty | Total Consumed Qty | Stock
Bricks        | Nos. | 300       | 50                 | 250
Cement        | Nos. | 600       | 100                | 500

So on above results Total Qty is to be fetched from Grn Table and Total Consumed Qty from Consumption Table and Stock is difference of both and should be Group By Material Name.

Below query where getting results for only Total Quantity and need your help to get the consumed quantity also.

I am new to SQL and did tried but stuck at this point and need help.

Select sm.material_name as 'Material Name', mu.unit_name as 'Unit Name' , sum(g.qty) as 'Total Qty' from grn g
JOIN material_table.sm ON g.material_id = sm.id
JOIN material_unit.mu ON g.unit = mu.id

GROUP by material_name

Answer 1

This answers the original version of the question.

Based on your sample data, I don't think a GROUP BY doesn't seem necessary. Your query and sample data are not consistent. But based on your question and the sample data:

select m.material_name, u.unit_name, grn.qty as total, c.qty as consumed,
       coalesce(gnr.qty, 0) - coalesce(c.qty, 0) as stock
from material mh join
     unit u
     on m.id = u.id left join
     grn
     on g.head_id = mh.id left join
     consumption c 
     on c.sub_material_id = m.id ;

If any of the tables have multiple rows per material, then this would not be the right solution. If that is the case, I would suggest that you ask a new question with appropriate sample data and desired results.