mysql - SQL Sum 没有返回正确的数据

Question

我的 Progress 数据库的示例，opdetail 表

invoice  invline    article   size    qty----------------------------------------905155   1          Shoe      10      5905155   2          Slipper   3       2905155   2          Slipper   4       6905155   2          Slipper   5       1905156   1          Boot      10      1 905156   1          Boot      11      1905157   1          Slipper   5       4       905157   2          Shoe      8       6

a simple SQL select statement, run from the OpenEdge editor returns just what I need, a list of invoices with their total quantities:-

SELECT invoice, sum(qty) FROM opdetail GROUP BY qty ORDER BY invoice ASC

905155  14905156   2905157  10

HOWEVER:-When run from an ASP page via DSN I have to list both fields in the GROUP BY otherwise progress returns a GROUP BY error

SELECT invoice, sum(qty) FROM opdetail GROUP BY qty, invoice ORDER BY invoice ASC

905155   5905155   9905156   2 905157   4905157   6

它没有总结数量，而且似乎考虑了行号，即使行号在我的 sql 语句中没有任何作用。任何人都可以对此有所了解，或者我如何在考虑行号的情况下对总数量求和？谢谢!

Answer 1

您在聚合函数中使用 qty 然后在 group by 上使用这没有任何意义，您应该在一些上使用 group by其他栏目作为

SELECT 
invoice, 
sum(qty) FROM opdetail 
GROUP BY invoice ORDER BY invoice ASC