how can we merge sum and count? [closed]

Question

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

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
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

Marco · Accepted Answer · 2016-01-15 14:03:05Z

2

SELECT   ref, AVG(quantity), AVG(price*quantity)
FROM     myTable
GROUP BY ref

answered Jan 15 '16 at 12:38

Marco

3,7403 gold badges18 silver badges24 bronze badges

add a comment |

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