Trying to sum numerous items in an inner joined query

[pce369]

So I have the following MySQL query, which works fine:

select 
year(created_at) as year, 
month(created_at) as month, 
day(created_at) as day, 
item_sdb_id,
price_per_unit

from 

    (select 
    l.item_sdb_id, 
    l.list_price / l.quantity as price_per_unit,
    l.created_at from listings as l 

        inner join 

    purchases as p on 
    l.purchase_id = p.id 

    where l.item_sdb_id in (12345) 
    and p.created_at > '2013-07-09') 

as t1
group by year, month, day

The problem is that instead of generating a price per unit of one item (12345 in this example) for each year, month, and day, I now want to sum the "list price/quantity" for a number of items (12345, 45278, 00154...), rendering a combined value of list prices/quantity per day, to essentially generate a daily index. I've come this far, but can't seem to make the next step -

[urtrivedi]

select 
year(created_at) as year, 
month(created_at) as month, 
day(created_at) as day, 
item_sdb_id,
price_per_unit
from 
    (select 
    l.item_sdb_id, 
    avg(l.list_price) / sum(l.quantity) as price_per_unit,
    l.created_at 
    from listings as l 
        inner join 
    purchases as p on 
    l.purchase_id = p.id 
    where l.item_sdb_id 
    and p.created_at > '2013-07-09' group by l.item_sdb_id,l.created_at) 
as t1
group by year, month, day

I assume that your listing table have multiple entries for item_sdb_id for each day.
So I am diving that day average list price with the total quantity of that day

[pce369]

Thank you - that got me a little closer. So, a little more information. For each item_sdb_id there will be a number of orders. For 54321, for example, there might be

10 (quantity) for $100 (list_price) - $10 price per unit
5 (quantity) for $25 (list_price) - $5 price per unit
.
.
.
for an average of $7.50 per unit

Then for 12345, there might be
17 (quantity) for $24 (list price)
etc.

I am trying to get the average per unit price (price/quantity) for all of the orders under 54321, 12345, individually...and then sum the lot of them. The aggregate, summed value of each average price per unit of the item_sdb_ids.

[urtrivedi]

can you post sample data with 5-10 records and expected sample output from same data. give sample with multiple items with multiple orders with mutliple days