0

If I have a table like this:

DATE        CATEGORY    QTY      SUM
01/01/2019     B         2       true
02/01/2019     B         3       true
03/01/2019     A         1       false
04/01/2019     B         3       true
05/01/2019     A         3       true
06/01/2019     A         2       true

I need to add the qty field by category, where the 'sum' column is true, sorting by the 'date' field. How can I get the following result using CTE in SQL Server

Result Query:

DATE        CATEGORY    QTY_TOTAL   SUM
01/01/2019     B           2        true
02/01/2019     B           5        true
03/01/2019     A           0        false
04/01/2019     B           8        true
05/01/2019     A           3        true
06/01/2019     A           5        true

PS: I looked for some similar question, but found none that could clarify my doubts completely.

Thanks in advance!

Marcoscdoni
  • 955
  • 2
  • 11
  • 31
  • So if there was a category B on the 3rd which was false, would the category B on the 4th still be 8 or would the false row constitue a break in the series making it 3? – Martin Brown May 14 '19 at 12:56
  • @MartinBrown If the sum column is false it just does not add, but does not subtract from the total, the sum would continue from the next line with value true – Marcoscdoni May 14 '19 at 12:59
  • So this is just a conditional cumulative sum, @Marcoscdoni. The duplicates marked will still answer the "hard part" of the question (how to do a cumulative sum); you just need to make it a conditional `SUM` by using a `CASE` expression. – Thom A May 14 '19 at 13:04

1 Answers1

1

You want cumulative sum : 

select t.*, sum(case when [sum] = 'true' then qty else 0 end) over (partition by category order by [date]) as QTY_TOTAL
from table t
order by [date];
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52