SQL/PHP - find average for sum of specific items

Question

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.

Gurwinder Singh · Accepted Answer · 2017-04-01 21:45:09Z

2

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.

edited Apr 1, 2017 at 21:45

answered Apr 1, 2017 at 20:24

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Szymon Pawelec Over a year ago

Works like a charm! Thank you!

Szymon Pawelec Over a year ago

Ok, I think I was to quick a little bit. It does not work as I wanted to. It searches for 5 lowest price rows right? But I need to find average price for 20 cheapest Apples including different quantities

Gurwinder Singh Over a year ago

@SzymonPawelec - Your question says - "I need to find average price for 5 cheapest Apples". If you want it for 20 cheapest apples, then you can just change the limit 5 to limit 20. If that is not what you want, please post the expected result with a bit more explanation.

Szymon Pawelec Over a year ago

That is correct. In my table there are 2 Apples for 20 USD, 1 Apple for 19 USD, 1 Apple for 19 USD, 1 Apple for 15 USD, 1 Apple for 21 USD, 4 Apples for 14 USD. So average price for 5 lowest price should be: 4 Apples for 14 USD + 1 Apple for 15 USD. So average price should be 14,2 USD. (it was 5 apples ofc, not 20 just like I said in my previous post)

Gurwinder Singh Over a year ago

@SzymonPawelec - Hoping that I understood your question correctly, I've updated the answer. Please try now?

|

Collectives™ on Stack Overflow

SQL/PHP - find average for sum of specific items

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related