mysql - 如何对具有多行的列的数量求和

Question

在下面的查询中，我尝试对每个唯一的 ii.item_desc 的 quantity(ii.qty) 求和。但还需要通过唯一的ven.name来做到这一点。我需要按每个唯一的 ven.name

获取每个唯一项目 desc 的总数的摘要

SELECT 
j.id AS "Job #",    
FROM_UNIXTIME(i.invoice_date, '%m-%d-%y') AS "Invoice Date",
c.name AS customer_name,
cl.name AS location_name,   
cl.store_nbr AS "Store #",
ven.name AS "MTC Contractor",
ws.label AS po_workflow_step,
cl.zone,
t.label AS item_type,
ws.label AS "PO Step",
addr.city AS "Store City",
addr.state_code AS "Store State",
addr.zip AS "Store Zip",
inventory_item.manufacturer_item_nbr AS "Item #",
ii.item_desc AS "Item Desc",
ii.qty,
ii.inventory_type,
FROM_UNIXTIME(j.completed_date, '%m-%d-%y') AS job_completed_date,
jt.label AS "Job Type",
ma.cycle AS "Maintenance Cycle"
FROM invoice_item ii
JOIN invoice i ON i.id = ii.invoice_id
JOIN `type` t ON t.id = ii.type_id
JOIN job j ON j.id = i.job_id
JOIN `type` jt ON jt.id = j.type_id
JOIN customer_location cl ON cl.id = j.customer_location_id
JOIN customer c ON c.id = cl.customer_id
LEFT JOIN maintenance ma ON cl.id = ma.customer_location_id
LEFT JOIN contractor con ON con.id = j.contractor_id
JOIN vendor ven ON ven.id = ma.vendor_id
JOIN address addr ON addr.id = cl.address_id
LEFT JOIN purchase_order po ON po.id = ii.purchase_order_id
LEFT JOIN workflow_step ws ON ws.id = po.workflow_step_id
LEFT JOIN purchase_order_work_detail wd ON wd.purchase_order_id = 
ii.purchase_order_id
LEFT JOIN inventory_item ON inventory_item.item_desc = ii.item_desc
WHERE i.invoice_date BETWEEN '1522540800' AND '1554076799'
AND (cl.store_nbr LIKE "%CTI%" OR cl.store_nbr LIKE "%TJX%" OR 
cl.store_nbr LIKE "%BEA%" OR cl.store_nbr LIKE "%OLL%")
AND ii.inventory_type LIKE "%Material%"
AND ven.name IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
AND ma.cycle LIKE "%May%"
AND ii.qty >'0'
AND ii.item_desc NOT IN (" 20' mast style Lift", "Material Pass Through 
Tax", "Material Freight", "*** Materials - Add details here ***", 
"Material", "Material per quote", "Breaker")

Answer 1

我认为您需要的是WITH ROLLUP。一般来说，GROUP BY 中变量的顺序并不重要，但由于我们在这里使用 WITH ROLLUP，因此您可以根据所需的级别更改顺序。聚合。

Select 

j.id AS "Job #",  

SUM(ii.qty) AS total_quantity,

FROM_UNIXTIME(i.invoice_date, '%m-%d-%y') AS "Invoice Date",
c.name AS customer_name,
cl.name AS location_name,   
cl.store_nbr AS "Store #",
ven.name AS "MTC Contractor",
ws.label AS po_workflow_step,
cl.zone,
t.label AS item_type,
ws.label AS "PO Step",
addr.city AS "Store City",
addr.state_code AS "Store State",
addr.zip AS "Store Zip",
inventory_item.manufacturer_item_nbr AS "Item #",
ii.item_desc AS "Item Desc",
ii.qty,
ii.inventory_type,
FROM_UNIXTIME(j.completed_date, '%m-%d-%y') AS job_completed_date,
jt.label AS "Job Type",
ma.cycle AS "Maintenance Cycle"
FROM invoice_item ii
JOIN invoice i ON i.id = ii.invoice_id
JOIN `type` t ON t.id = ii.type_id
JOIN job j ON j.id = i.job_id
JOIN `type` jt ON jt.id = j.type_id
JOIN customer_location cl ON cl.id = j.customer_location_id
JOIN customer c ON c.id = cl.customer_id
LEFT JOIN maintenance ma ON cl.id = ma.customer_location_id
LEFT JOIN contractor con ON con.id = j.contractor_id
JOIN vendor ven ON ven.id = ma.vendor_id
JOIN address addr ON addr.id = cl.address_id
LEFT JOIN purchase_order po ON po.id = ii.purchase_order_id
LEFT JOIN workflow_step ws ON ws.id = po.workflow_step_id
LEFT JOIN purchase_order_work_detail wd ON wd.purchase_order_id = 
ii.purchase_order_id
LEFT JOIN inventory_item ON inventory_item.item_desc = ii.item_desc
WHERE i.invoice_date BETWEEN '1522540800' AND '1554076799'
AND (cl.store_nbr LIKE "%CTI%" OR cl.store_nbr LIKE "%TJX%" OR 
cl.store_nbr LIKE "%BEA%" OR cl.store_nbr LIKE "%OLL%")
AND ii.inventory_type LIKE "%Material%"
AND ven.name IN ('A', 'B', 'C', 'D', 'E', 'F', 'G')
AND ma.cycle LIKE "%May%"
AND ii.qty >'0'
AND ii.item_desc NOT IN (" 20' mast style Lift", "Material Pass Through 
Tax", "Material Freight", "*** Materials - Add details here ***", 
"Material", "Material per quote", "Breaker")

GROUP BY
ven.name, ii.item_desc WITH ROLLUP;