-1

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
Mayur Patel
  • 11
  • 4
  • Your query refers to tables that are not shown. Conversely, you show tables that are not in the query. – Gordon Linoff May 02 '20 at 12:07
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. – philipxy May 03 '20 at 21:17

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes Gordon, It's correct that my table contains multiple entry and that needs to be group by material name. However i have corrected the data and the query which i have mentioned on the original post – Mayur Patel May 02 '20 at 12:15
  • Can you please consider the latest edit on the original post and based on that if you can help. – Mayur Patel May 02 '20 at 12:23
  • @MayurPatel . . . I think I was pretty clear in the answer that you should ask a NEW question. – Gordon Linoff May 02 '20 at 22:21