title: How can I calculate daily snapshots of my total sales on SQL?
body: <p>I have a table (let's call it DiodeSales) that tells me the total number of diode sales I made, grouped by date, diode color, and country. This is a sample of this schema:</p>
  
  <pre>
  Date                      Color   Country   Sales
  June, 20 2016 00:00:00    Green   US        1        
  June, 20 2016 00:00:00    Red     Japan     1        
  June, 20 2016 00:00:00    Red     US        1        
  June, 21 2016 00:00:00    Red     US        1        
  June, 22 2016 00:00:00    Green   US        1        
  June, 22 2016 00:00:00    Red     US        1        
  June, 23 2016 00:00:00    Green   US        1        
  June, 23 2016 00:00:00    Red     Japan     1        
  June, 23 2016 00:00:00    Red     US        1        
  June, 24 2016 00:00:00    Green   US        1        
  June, 24 2016 00:00:00    Red     US        1            
  </pre>
  
  <p>I want to be able to have have an additional column that tells me how many diodes we've sold up until that point. So, for example, using the above data, the {June 23, Red, 1, US} row would have a total sales value of 4, because we've sold 4 red diodes in the US at that point.</p>
  
  <p>I initially thought a cumulative sum would do the trick. So I wrote this: (sqlfiddle <a href="http://sqlfiddle.com/#!9/c949d/8" rel="nofollow noreferrer">here</a>)</p>
  
  <pre><code>SELECT
    t1.Date,
    t1.Color,
    t1.Country,
    t1.Sales,
    SUM(t2.Sales) AS CumulativeSales
  FROM DiodeSales AS t1
  INNER JOIN DiodeSales AS t2
  ON t1.Date &gt;= t2.Date
    AND t1.Color = t2.Color
    AND t1.Country = t2.Country
  GROUP BY
    t1.Date,
    t1.Color,
    t1.Country
  </code></pre>
  
  <p>This gives me the cumulative sum, as expected, but it does not give me the total sales for a given color in a given country on a given day. In particular, because some specific days may have 0 sales in some country, they will not have a cumulative value associated to it. For example, consider the results of the previous table:</p>
  
  <pre>
  Date                      Color   Country   Sales    CumulativeSales
  June, 20 2016 00:00:00    Green   US        1        1
  June, 20 2016 00:00:00    Red     Japan     1        1
  June, 20 2016 00:00:00    Red     US        1        1
  June, 21 2016 00:00:00    Red     US        1        2
  June, 22 2016 00:00:00    Green   US        1        2
  June, 22 2016 00:00:00    Red     US        1        3
  June, 23 2016 00:00:00    Green   US        1        3
  June, 23 2016 00:00:00    Red     Japan     1        2
  June, 23 2016 00:00:00    Red     US        1        4
  June, 24 2016 00:00:00    Green   US        1        4
  June, 24 2016 00:00:00    Red     US        1        5
  </pre>
  
  <p>If I were to look for the column corresponding to Japan on June 24, I'd find nothing (because there was no Japan sale that day, so there is no Japan row for that day). I don't think there's a way to do this in SQL, but is it possible to populate this resulting table with values on days in which some countries had no sales? The starting table will always have at least one column for each day for some country.</p>
  
  <p>I am aware I could just write a simple</p>
  
  <pre>
  SELECT SUM(Sales) FROM DiodeSales
  WHERE Date &lt= @someDate AND Color = @someColor AND Country = @someCountry
  </pre>
  
  <p>to get this information, but this is for a table that has to be formatted in that way for it to be used by another piece of already-made software.</p>
  
  <p>EDIT: Someone mentioned this as a potential duplicate of  Calculate a <a href="https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server">Running Total in SQL Server</a>, but that post only addresses efficiency while calculating a running sum. I already have various ways of calculating this sum, but I'm looking for a way to fix the issue of missing day/country combinations for days when there were no sales in that country. For the above example, the fixed query would return this:</p>
  
  <pre>
  Date                      Color   Country   Sales    CumulativeSales
  June, 20 2016 00:00:00    Green   US        1        1
  June, 20 2016 00:00:00    Red     Japan     1        1
  June, 20 2016 00:00:00    Red     US        1        1
  June, 21 2016 00:00:00    Green   US        0        1
  June, 21 2016 00:00:00    Red     Japan     0        1
  June, 21 2016 00:00:00    Red     US        1        2
  June, 22 2016 00:00:00    Green   US        1        2
  June, 22 2016 00:00:00    Red     Japan     0        1
  June, 22 2016 00:00:00    Red     US        1        3
  June, 23 2016 00:00:00    Green   US        1        3
  June, 23 2016 00:00:00    Red     Japan     1        2
  June, 23 2016 00:00:00    Red     US        1        4
  June, 24 2016 00:00:00    Green   US        1        4
  June, 24 2016 00:00:00    Red     Japan     0        2
  June, 24 2016 00:00:00    Red     US        1        5
  </pre>