CODEWITHSUNDEEP
mysqlsql
Ghostman
Ghostman

Reputation: 6114

How to do sum of sum in mysql query

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

output present

supplier    total_qty
12046      475.00
12482      99.00

output needed

total_qty
574.00

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

Upvotes: 10

Views: 17925

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Just modify GROUP 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

Output:

supplier    total_qty
12046       475.00
12482        99.00
NULL        574.00

Upvotes: 18

Yahia
Yahia

Reputation: 70369

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 comment from OP changing the result structure:

For an additional 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 additonal row:

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

Upvotes: 3

redmoon7777
redmoon7777

Reputation: 4526

try without using the group by since you want to sum every thing

Upvotes: 1

John Woo
John Woo

Reputation: 263693

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

Upvotes: 7

Related Questions