My result of the request of group by is :
ref state quantity total
PO3253 assinged 300.0 323400
PO3253 done 300.0 323400
When I delete state from group by using:
SELECT ref, sum(quantity),sum(price*quantity) as total
FROM myTable
GROUP BY ref
I get:
ref quantity total
PO3253 600.0 646800
I want the result to be:
ref quantity total
PO3253 300.0 323400
I want to do something like this operation :
SELECT ref, sum(quantity)/count(*),sum(price* sum(quantity) / count(*))
FROM myTable
GROUP BY ref
SUM
divided byCOUNT
is calledAVERAGE
average(price*quantity)
but we can't be sure.AVG(price*quantity)
orSUM(price*quantity)/SUM(quantity)
orSUM(price*quantity)/SUM(price)
or ...? There are many possible scenarios for such queries.