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

closed as unclear what you're asking by ypercubeᵀᴹ, mustaccio, Philᵀᴹ, Erwin Brandstetter, Paul White Jan 16 '16 at 0:22

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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

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