SQLTeam.com | Weblogs | Forums

Help needed with this query (Running Counts, grouped by Criteria)


#1

I have this table that describes how the total quantity of articles ordered in a line can be split into smaller lots:

ID     Line    Tot    QTY
1      10      3      3
2      20      10     4
3      20      10     4
4      20      10     2
5      30      5      5
6      40      8      4
7      40      8      4

In order to process serial numbers I need to get for each ID the amount of items (#runningCount) already covered by previous Id's belonging to the same line. So I need to get this somehow:

ID     Line    Tot    QTY  #runningCount
1      10      3      3    0
2      20      10     4    0
3      20      10     4    4
4      20      10     2    8
5      30      5      5    0
6      40      8      4    0
7      40      8      4    4

who can give me the right hint?


#2

Ok, I could do it by myself. Sometimes the brain just drives you in the wrong direction:

Select T.ID, T.Line, T.Tot, T.QTY, SUM(T2.QTY) as RunningCount
FROM Table T
LEFT OUTER JOIN TABLE T2 on T.ID > T2.ID and T.Line = T2.Line
GROUP BY T.ID, T.Line, T.Tot, T.QTY
ORDER BY T.Line, T.ID