我的查询:

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/

10-11 03:23