我的查询:
SELECT
tm.ManufacturerName,sum(tb.Quantity) AS qty,
tb.AfterDiscount AS AfterDiscount
FROM tblbasket AS tb
INNER JOIN tblstock AS ts
ON ts.ProductCode = tb.ProductCode
AND tb.Status=3
LEFT JOIN tblmanufacturer AS tm
ON tm.ManufacturerID=ts.ManufacturerID
GROUP BY ts.ManufacturerID,AfterDiscount
ORDER BY qty desc
输出此:
ManufacturerName qty AfterDiscount
MOSER BAER HOME VIDEO 48 57.6
MOSER BAER HOME VIDEO 39 28.8
MOSER BAER HOME VIDEO 28 115.2
MOSER BAER HOME VIDEO 27 259.2
MOSER BAER HOME VIDEO 26 374.4
SAREGAMA INDIA LIMITED 25 135
SYMPHONY HOME VIDEO 22 36
MOSER BAER HOME VIDEO 20 144
SAREGAMA INDIA LIMITED 19 1282.5
SAREGAMA INDIA LIMITED 18 67.5
MOSER BAER HOME VIDEO 18 172.8
MOSER BAER HOME VIDEO 16 460.8
SYMPHONY HOME VIDEO 16 45
RAJ VIDEO VISION 16 71.1
MOSER BAER HOME VIDEO 15 86.4
SAREGAMA INDIA LIMITED 15 202.5
MASTER ACCESSORIES 13 90
MOSER BAER HOME VIDEO 13 89.1
MELODY RECORDING 13 31.5
MOSER BAER HOME VIDEO 13 27
我想要的是:
ManufacturerName qty AfterDiscount
MOSER BAER HOME VIDEO 190 1568
SAREGAMA INDIA LIMITED 77 1686
SYMPHONY HOME VIDEO 38 81
RAJ VIDEO VISION 16 71.1
MASTER ACCESSORIES 13 90
MELODY RECORDING 13 31.5
我的查询没有显示正确的结果。我应该更改查询什么?
我想我应该对数量和价格求和,并按数量和价格分组,并按数量排序。
有什么想法或建议吗?
最佳答案
删除AfterDiscount组。这为所有ManufacturerId,AfterDiscount组合提供了唯一的一行,这是您的输出所反映的。
您只需要GROUP BY ManufacturerID和SELECT SUM(AfterDiscount)即可汇总该列和任何其他需要汇总的列。
SELECT tm.ManufacturerName,sum(tb.Quantity) as qty,SUM(tb.AfterDiscount) as AfterDiscount from tblbasket as tb inner join tblstock as ts on ts.ProductCode = tb.ProductCode and tb.Status=3 left join tblmanufacturer as tm on tm.ManufacturerID=ts.ManufacturerID
group by ts.ManufacturerID
order by qty desc
关于mysql - mysql组通过使用两列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/4422427/