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
这里我需要这个查询中的总和(total_qty)?如何实现这一目标
4 个解决方案
#1
15
Just modify GROUP BY
, adding WITH ROLLUP
:
只需修改GROUP BY,添加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
#2
4
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
#3
3
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:
编辑2 - 来自OP的评论更改结果结构:
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
#4
1
try without using the group by since you want to sum every thing
尝试不使用该组,因为你想要总结每一件事
#1
15
Just modify GROUP BY
, adding WITH ROLLUP
:
只需修改GROUP BY,添加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
#2
4
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
#3
3
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:
编辑2 - 来自OP的评论更改结果结构:
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
#4
1
try without using the group by since you want to sum every thing
尝试不使用该组,因为你想要总结每一件事