Search code examples
sqlsubquery

SQL sum with Group By of subquery max

I have a GL summary table for inventory. It has multiple rows for each item(last month period that the item changes). I can easily get the current value of the sum (qty*price) by inner join (select max(post-date). So the valuation is sum(qty )So if you want the valuation for say post period 202103, you would take the max(post-date) for each partnumber and sum it togeher. For 202103 it would be PartA = 10, PartB 62 and PartC 12. If I wanted to get it for post period 202012 then it would be partA 14, PartB 62 and PartC 12. Essentially uusing the max date of 202012 for PartA. So i have the query for doing the max for the entire table (most up to date). But how would I go about it to get it for say each period. I've tried various group by in the subquery and everything else, but cant seem to make this work.

part#  qty post_date
PartA  23  202004
PartB  55  202004
PartC  12  202004
PartA  20  202006
PartB  62  202007
PartA  14  202012
PartA  12  202102
PartA  10  202103

select
    sum(qty)
from
    invtable inv
INNER JOIN (
    Select
        max(post_date)
    from
        invtable inv2
    WHERE
        post_date < 202103
    GROUP BY
        partnumber
) as t2 
    ON t2.partnuumber = inv.partnumber
    and t2.post_date = inv.post_date

Ideally I want something like the following

202004 90
202006 87
202007 94
202012 88
202102 86
202103 84

Any help or suggestions would be greatly appreciated. If I cant get it to output this way, I guess I could programmatically run each period and dump it to a table and then pull from there.

Solution

  • I tried this in mysql. Below is the query. Please see if you can change or use this according to your database.

    -- to find the sum of qty 
select
    finalT2.post_date, sum(finalT1.qty)
from
    invtable finalT1
    join 
    (
      -- for each post date -> find the max of post date for each part
      select
        t2.post_date, t1.part, max(t1.post_date) as max_post_date
      from
        invtable t1
        join 
        (  -- group to find the list of post_date(s)
           select
         post_date
           from
         invtable
           group by
         post_date
         ) t2 on t1.post_date <= t2.post_date
       group by
         t2.post_date, t1.part
    ) finalT2 on finalT1.post_date = finalT2.max_post_date and finalT1.part = finalT2.part
group by
    finalT2.post_date

    Output Image - enter image description here

    Query output in Fiddle for reference : https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=18657bfb3ec0f8d4826a8c940ecc9941