mysql - SQL query to SUM sales but only SUM most recent on hand for a given GROUP -

i'm having issues figuring out how group table in various ways suming numbers of units sold, suming recent on hand units each item within each group.

here's example dataset: http://www.sqlfiddle.com/#!2/3ff18/1

i need able perform group bys in such way on hand column sumed recent item(s) within each group.

i've had progress "self join" using max(date), i'm not getting desired results when using various group bys.

here's example outputs i'd see given sqlfiddle.com dataset:

category     sold  on hand electronics   500        0 books         500        0 other           0      100  quarter  category     sold  on hand q1       electronics   400      100 q1       books         400      100 q1       other           0      100 q2       electronics   100        0 q2       books         100        0 q2       other           0      100  month    sold  on hand  january   300      800  february  100      700  march     200      500  april     200      300  may         0      300 <- may entry isn't strickly necessary, nice june      100      200  july      100      100 <- 100 units on hand item 987 hasn't been sold 

one area max(date) approach tripping me group month. if in table above you'll note i'd see 100 units on hand in july... units have been sold except item 987 added in january, has not sold.

a couple of notes:

• this using mysql it'd willing try postgresql if has windowing functions assist this.
• the performance of solution vital given there 1.5 million records. , see millions more added.

in postgres, have a variety of window functions chose from.

you have distinct on pick row greatest n in column per group:
select first row in each group group?

and have functions date / time algebra , formatting (most of have in mysql well). there no point in storing month , quarter redundantly. bloats table , slows down. adapted table layout accordingly. see fiddle below.

working accordingly simplified postgres table:

create table tbl (    item int   ,on_hand int   ,sold int   ,thedate date   ,category text ); 

demo extract() & to_char():

select extract(quarter thedate)::int quarter_int      , extract(month   thedate)::int month_int      , to_char(thedate, '"q"q')  quarter_text      , to_char(thedate, 'month') month_text   tbl limit 1; 

get recent row each (item, month):

select distinct on (item, date_trunc('month', thedate))        *   tbl order  item, date_trunc('month', thedate), thedate desc; 

total count per category:

select category, sum(sold) sold, min(on_hand) on_hand  (    select category, sold         , first_value(on_hand) on (partition item                                      order thedate desc) on_hand    tbl    ) sub group  1 order  1; 

the same category , month:

select category, to_char(month, 'yyyy-mon') month      , sum(sold) sold, min(on_hand) on_hand  (    select category, date_trunc('month', thedate) month, sold         , first_value(on_hand) on (partition item, date_trunc('month', thedate)                                      order thedate desc) on_hand    tbl    ) sub group  1, sub.month order  1, sub.month; 

sql fiddle demo.