Determine monthly values of timestamped records

I have a SQL table with the following schema:

fruit_i开发者_运维知识库d INT 
price    FLOAT
date     DATETIME

This table contains many records where the price of a given fruit is recorded at a given time. There may be multiple records in a single day, there may be

I would like to be able to fetch a list of prices for a single fruit over the last 12 months inclusive of the current month. So given a fruit_id of 2 and datetime of now(), what would the price values be for December, January, February, ... October, November?

Given the above requirements, what strategy would you use to get this data? Pure sql, fetch all prices and process in code?

Thanks for you time.

Are you talking about min price, max price, average price, or something else?

Here's a quick query to get you started, which includes min, max, and average price for each month for fruit_id 2:

select left(date,7) as the_month, min(price),max(price),avg(price)
from fruit_price
where fruit_id = 2
and date >= concat(left(date_sub(curdate(), interval 11 month),7),'-01')
group by the_month;

If I understand it correctly from -

I would like to be able to fetch a list of prices for a single fruit over the last 12 months inclusive of the current month. So given a fruit_id of 2 and datetime of now(), what would the price values be for December, January, February, ... October, November?

You want the total price for every month for a single year based on the date and fruit_if you pass in.

So,this won't give all months of an year but all months which had a price for year..in case you want all months..you would need to create a dimdate table which will have all the dates...and then join with it..

    declare @passeddate=Now() --date to be calculated
    declare @fruit_id=2 --fruit id to be calculated
    Select 
          fruit_id as FruitId,
          Sum(price) as MonthPrice,
          Month(date)  as FruitMonth
    from SQL_Table 
    group by FruitMonth,FruitId  
    where fruit_id=@fruit_id and
    Year(date)=Year(@passeddate)

select month(date) as "Month", distinct(price) as "Unique Price" where fruit_id = 2 group by month(date);

I'd try to state as much as possible in SQL that does not require unindexed access to data because it's usually fast(er) than processing it with the application.