How to sum data group by week but sum from beginning?

Question

I have a database like this :

Entry_No    Week    Registering_Date    Bin_Code    Item_No  Quantity
=====================================================================
1           26      6/26/2015           BIN 1       A         10
2           26      6/26/2015           BIN 1       B         20
3           26      6/26/2015           BIN 1       C         30
4           26      6/26/2015           BIN 1       D         40 
5           27      6/29/2015           BIN 1       A         -3 
6           27      6/29/2015           BIN 2       A          3  
7           27      6/29/2015           BIN 1       A         -2
8           27      6/29/2015           BIN 3       A          2
9           28       7/5/2015           BIN 1       B        -15
10          28       7/5/2015           BIN 3       B         15
11          28       7/5/2015           BIN 1       C        -25
12          28       7/5/2015           BIN 2       C         25
13          28       7/5/2015           BIN 1       B         50

And I would like to sum group by BIN_CODE and ITEM_NO by WEEK but from beginning of the data.. I know how to sum group by week but it only shows the summary on that week (not from beginning)

And the result i expect is like this :

WEEK  BIN CODE  ITEM NO  QUANTITY
====================================
26    BIN 1     A        10 
26    BIN 1     B        20 
26    BIN 1     C        30 
26    BIN 1     D        40 
26    BIN 2     A         -   
26    BIN 2     B         -   
26    BIN 2     C         -   
26    BIN 2     D         -   
26    BIN 3     A         -   
26    BIN 3     B         -   
26    BIN 3     C         -   
26    BIN 3     D         -   
27    BIN 1     A         5 
27    BIN 1     B        20 
27    BIN 1     C        30 
27    BIN 1     D        40 
27    BIN 2     A         3 
27    BIN 2     B         -   
27    BIN 2     C         -   
27    BIN 2     D         -   
27    BIN 3     A         2 
27    BIN 3     B         -   
27    BIN 3     C         -   
27    BIN 3     D         -   
28    BIN 1     A         5 
28    BIN 1     B         55 
28    BIN 1     C         5 
28    BIN 1     D         40 
28    BIN 2     A         3 
28    BIN 2     B         -   
28    BIN 2     C         25 
28    BIN 2     D         -   
28    BIN 3     A         2 
28    BIN 3     B        15 
28    BIN 3     C         -   
28    BIN 3     D         -

Sorry im newbie here to write a good question.. Here is the image :

enter image description here

Could you please help me?

Thanks before :)

This is not database.Its table – Sabyasachi Mishra Jul 03 '15 at 12:57 — Sabyasachi Mishra, Jul 03 '15 at 12:57

score 0 · Answer 1 · answered Jul 03 '15 at 13:31

Try this query

select Week,Registering_Date,Bin_Code,Item_No, case when asum>0 then asum else '-' end as QUANTITY
from(
select Week,Registering_Date,Bin_Code,Item_No,sum(case when Quantityfrom>0 then quentity else o end ) as asum
from tablename
group by Week,Bin_Code,Item_No)a

score 0 · Accepted Answer · answered Jul 03 '15 at 13:38

Hope this works :

SELECT 
    t.Week AS WEEK,
    t.Bin_Code AS BIN_CODE,
    t.Item_No AS ITEM_NO,
    SUM(CASE WHEN items.Item_No = t.Item_No THEN items.Quantity ELSE 0 END) AS QUANTITY
FROM your_table as t
CROSS JOIN (SELECT DISTINCT Item_No FROM your_table) AS items
GROUP BY 
    t.Week,
    t.Bin_Code
ORDER BY
    1,2,3

How to sum data group by week but sum from beginning?

2 Answers2