How to make sum amount in mysql query

Question

SELECT a.id AS supplier, sum( processed_weight ) AS total_qty FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' GROUP BY b.supplier 

conclusion

 supplier total_qty 12046 475.00 12482 99.00 

conclusion required

 total_qty 574.00 

here i need the sum (total_qty) in this query? how to achieve this

Answer 1

Just change GROUP BY by adding WITH ROLLUP :

 SELECT a.id AS supplier, sum( processed_weight ) AS total_qty FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' GROUP BY b.supplier WITH ROLLUP 

Conclusion:

 supplier total_qty 12046 475.00 12482 99.00 NULL 574.00 

Answer 2

to try

 SELECT sum( processed_weight ) AS total_qty FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' 

EDIT 2 - AFTER a comment from OP, changing the structure of the result:

For an extra column, try:

 SELECT X.supplier, X.total_qty, (SELECT sum( processed_weight ) FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38') AS totalq FROM ( SELECT a.id AS supplier, sum( processed_weight ) AS total_qty, FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' GROUP BY b.supplier) AS X 

For an additional line:

 SELECT a.id AS supplier, sum( processed_weight ) AS total_qty FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' GROUP BY b.supplier UNION ALL SELECT null, X.total_qty FROM ( SELECT sum( processed_weight ) AS total_qty FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' ) AS X 

Answer 3

how about this:

 SELECT SUM(iQuery.total_qty) as iTotal FROM (SELECT a.id AS supplier, sum( processed_weight ) AS total_qty FROM supplier_inward a INNER JOIN warehouseb ON a.id = b.supplier WHERE a.master_product_id = '38' GROUP BY b.supplier) as iQuery 

Answer 4

try without using a group, since you want to summarize each thing