CODEWITHSUNDEEP
sqlmathsumaverage
pierreaurelemartin
pierreaurelemartin

Reputation: 1682

Get the average with quantity

I have a problem to calculate easily some simple average. My table :

id / user / action     / data
1  / a    / unit_price / 40
2  / a    / quantity   / 1
3  / b    / unit_price / 70
4  / b    / quantity   / 2

Unit_price is a price for a user and quantity is quantity. So there i should get : (40 + 70 + 70) / 3 = 60

If i do an

(AVG(action) WHERE action = unit_price)

I get :

(70+40)/2 = 55

If I do an

(SUM(action) WHERE action = unit_price) / (SUM(action) WHERE action = quantity)

I get :

110 / 3 = 36.6

The easiest way I found is to don't put the unit_price but the global price then make a division in the PHP code to get the unit_price, but I was hoping SQL could do something for me.

Upvotes: 2

Views: 4375

Answers (6)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

select coalesce(sum(quantity * unit_price) /sum(quantity), 0) from
(select 
   sum(case when action='unit_price' then data else 0 end) as unit_price,
   sum(case when action='quantity' then data else 0 end) as quantity 
 from test
group by user) as a

SqlFiddle

Upvotes: 7

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112299

I would join the table to itself in order to get the two records beloning together on one line

SELECT
    SUM(unit_price * quantity) / SUM(quantity) AS average_unit_price
FROM
    (SELECT
        U.data AS unit_price, Q.data AS quantity
    FROM
        theTable U
        INNER JOIN theTable Q
            ON U.user = Q.user
    WHERE
        U.action = 'unit_price' AND
        Q.action = 'quantity')

If you have more than two records per user and the ids of the both records are consequtive, then you would have to change the WHERE-clause to

    WHERE
        U.action = 'unit_price' AND
        Q.action = 'quantity' AND
        U.id + 1 = Q.id

Note:

If you calculate AVG(unit_price * quantity) you get the average sum per user.

(1*40 + 2*70) / 2 = 90

If you calculate SUM(unit_price * quantity) / SUM(quantity) you get the average unit price.

(1*40 + 2*70) / 3 = 60

Upvotes: 2

Taryn
Taryn

Reputation: 247670

You can use something like this which basically pivots the data to a more usable format and then gets the values that you need:

select avg(unit_price) AvgUnitPrice, 
  sum(unit_price*quantity)/sum(quantity) AvgPrice
from
(
  select user,
    max(case when action = 'unit_price' then data end) unit_price,
    max(case when action = 'quantity' then data end) quantity
  from table1
  group by user
) x;

See SQL Fiddle With Demo

Upvotes: 3

Lamak
Lamak

Reputation: 70638

Ok, obviously your table design is not optimal, you should have unit_price and quantity as separate columns. But, working with what you have, try this:

SELECT SUM(A.data*B.data)/SUM(B.data) Calculation
FROM (  SELECT user, data
        FROM YourTable
        WHERE action = 'unit_price') AS A
INNER JOIN (SELECT user, data
            FROM YourTable
            WHERE action = 'quantity') AS B
ON A.user = B.user

Upvotes: 2

pero
pero

Reputation: 4259

Your table design doesn't look good.

Make 2 tables instead:

ITEM
   ItemId int not null PK, 
   Name varchar(200) not null, 
   UnitPrice decimal (10,2) not null

SALES
   SalesId int not null PK, 
   ItemId int not null FK, 
   Quantity decimal(10,2)

PK - primary key, FK - foreign key

Average: 

select 
  I.Name, avg(I.UnitPrice * S.Quantity) as avgSales
from 
  Sales S 
  join Items I on I.ItemId = S.ItemId
group by 
  I.Name

Upvotes: 0

Jim
Jim

Reputation: 6881

Something like this should work; syntax might not be perfect since I didn't try it out, but you get the main idea at least.

SELECT sumUnitPrice.sum / sumQuantity.sum
FROM
(
  (SELECT SUM(data) as sum
  FROM WhateverTheHellYourTableIsNamed
  WHERE action = 'unit_price') sumUnitPrice

  (SELECT SUM(data) as sum
  FROM WhateverTheHellYourTableIsNamed
  WHERE action = 'quantity') sumQuantity
)

Upvotes: 0

Related Questions