select - MySQL: Selecting distinct with aggregate functions -

select - MySQL: Selecting distinct with aggregate functions -

i'm having problem selecting distinct order id orderitems table.

my orderitems table contains: orderid | itemid | orderedquantity | instockquantity | backorderquantity | isbackorderofid | id.

orderedquantity total ordered amount, instockquantity quantity available @ time of purchase , backorderquantity rest. isbackorderofid correlates id column when order item backorder of order item.

my query trying select distinct orderid of entries backorderquantity < sum(instockquantity) isbackorderofid = id. result in list if distinct order ids have remaining backorders shipped.

test data:

orderid | itemid | orderedquantity | instockquantity | backorderquantity | isbackorderofid | id 1 | 114 | 10 | 6 | 4 | 0 | 23 1 | 255 | 4 | 3 | 1 | 0 | 24 2 | 114 | 3 | 3 | 0 | 23 | 25

query:

select items1.* orderitems items1 left bring together orderitems items2 on items2.isbackorderofid = items1.id items1.backorderquantity > '0' grouping items2.isbackorderofid having sum(items2.instockquantity) < items1.backorderquantity or sum(items2.instockquantity) null`

this results in both first , sec row has same orderid. can't utilize select distinct because of utilize of aggregate functions.

any help appreciated.

select items1.orderid orderitems items1 left bring together orderitems items2 on items2.isbackorderofid = items1.id grouping items1.orderid having coalesce(sum(items2.instockquantity), 0) < sum(items1.backorderquantity)

mysql select distinct