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

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
  • 5
    SUM divided by COUNT is called AVERAGE – Remus Rusanu Jan 15 '16 at 12:34
  • Show us the actual query and some sample data, not just the output. I fear there are some details missing, which are crucial to solving the issue. @Remus hinted on the problem, you may simply want average(price*quantity) but we can't be sure. – ypercubeᵀᴹ Jan 15 '16 at 12:45
  • @YperSillyCubeᵀᴹ AVG works for this example. I cannot post the details it's a complicated request. – m3asmi Jan 15 '16 at 15:01
  • 1
    So you don't mind getting irrelevant answers? We don't mind complicated queries, on the contrary. Without details, I have voted for this to be closed. Do you really don't care whether the answer is AVG(price*quantity) or SUM(price*quantity)/SUM(quantity) or SUM(price*quantity)/SUM(price) or ...? There are many possible scenarios for such queries. – ypercubeᵀᴹ Jan 15 '16 at 17:26
SELECT   ref, AVG(quantity), AVG(price*quantity)
FROM     myTable
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.