CODEWITHSUNDEEP
mysqlsql
Szymon Pawelec
Szymon Pawelec

Reputation: 11

SQL/PHP - find average for sum of specific items

Well, SQL is not my native language and I can't handle with my problem. I have one table.

+------------+--------+-----------+
| Item name: | Price: | Quantity: |
+------------+--------+-----------+
| Cherry     |     20 |        11 |
| Cherry     |     19 |         9 |
| Apple      |     20 |         2 |
| Apple      |     19 |         1 |
| Cherry     |     16 |         2 |
| Apple      |     15 |         1 |
| Apple      |     21 |         1 |
| Apple      |     14 |         4 |
| Cherry     |     11 |         1 |
+------------+--------+-----------+

I need to find average price for 5 cheapest Apples. Thanks for any help.

Upvotes: 1

Views: 61

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

If I understand correctly, you want to get first 5 apples with least price and find weighted average of price. You can use user variables here.

select
    sum(price * quantity) / sum(quantity) as average_price
from (
    select
        price,
        case when diff >= 0 then quantity else quantity + diff end as quantity
    from (
        select t.*,
            @required := @required - quantity as diff
        from your_table t
        cross join (select @required := 5) x
        where item_name = 'Apple'
        order by price
    ) t where quantity + diff > 0
) t;

See this demo - http://rextester.com/XZQ74947

You can run the subqueries independently to see how it's actually working.

Upvotes: 2

Related Questions