SQL Sum not returning the correct data

[user2941871]

An example of my Progress database, opdetail table


invoice invline article size qty
----------------------------------------
905155 1 Shoe 10 5
905155 2 Slipper 3 2
905155 2 Slipper 4 6
905155 2 Slipper 5 1
905156 1 Boot 10 1
905156 1 Boot 11 1
905157 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 14
905156 2
905157 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 5
905155 9
905156 2
905157 4
905157 6


Its not summarizing the qty, and seems to be taking into account the line number even though the line number plays no part in my sql statement. Can anyone throw any light on this or how I can do a sum of the total qty taking into account the line number? Thanks!

Continue reading...