SQL: How to make a conditional COUNT and SUM

Let's say I have the following table:

id    | letter   | date   
--------------------------------
1     | A        | 2011-01-01
2     | A        | 2011-04-01
3     | A        | 2011-04-01
4     | B       开发者_开发问答 | 2011-01-01
5     | B        | 2011-01-01 
6     | B        | 2011-01-01 

I would like to make a count of the rows broken down by letter and date, and sum the count of all the previous dates. every letter should have a row to every date of the table (ie. letter B doesn't have a 2011-04-01 date, but still appears in the result)

The resulting table would look like this

letter| date           | total  
--------------------------------
A     | 2011-01-01     | 1
A     | 2011-04-01     | 3
B     | 2011-01-01     | 3 
B     | 2011-04-01     | 3

How to achieve this in a SQL query? Thank you for your help!

NOTE I didn't notice it was mysql, which doesn't support CTE. You may be able to define temporary tables to use this.

This is an interesting problem. You kind of need to join all letters with all dates and then count the preceding rows. If you weren't concerned with having rows for letters that have a count of 0 for the dates, you could probably just do something like this:

SELECT letter, date, 
    (SELECT COUNT(*) 
     FROM tbl tbl2 
     WHERE tbl2.letter = tbl1.letter
        AND tbl2.date <= tbl1.date) AS total
FROM tbl
ORDER BY date, letter

/deleted CTE solution/

Solution without CTE

SELECT tblDates.[date], tblLetters.letter, 
    (SELECT COUNT(*)
     FROM tblData tbl2
     WHERE tbl2.letter = tblLetters.letter
        AND tbl2.[date] <= tblDates.[date]) AS total
FROM (SELECT DISTINCT [date] FROM tblData) tblDates
    CROSS JOIN (SELECT DISTINCT letter FROM tblData) tblLetters
ORDER BY tblDates.[date], tblLetters.letter

The requirement

every letter should have a row to every date of the table

requires a cross join of the distinct dates and letters. Once you do that its pretty straight forward

SELECT letterdate.letter, 
       letterdate.DATE, 
       COUNT(yt.id) total 
FROM   (SELECT letter, 
               date 
        FROM   (SELECT DISTINCT DATE 
                FROM   yourtable) dates, 
               (SELECT DISTINCT letter 
                FROM   yourtable) letter) letterdate 
       LEFT JOIN yourtable yt 
         ON letterdate.letter = yt.letter 
            AND yt.DATE < yt.letter 
GROUP  BY letterdate.letter, 
          letterdate.DATE 

A slight variation on the previous:

    declare @table1 table (id int, letter char, date smalldatetime)
    insert into @table1 values (1, 'A', '1/1/2011')
    insert into @table1 values (2, 'A', '4/1/2011')
    insert into @table1 values (3, 'A', '4/1/2011')
    insert into @table1 values (4, 'B', '1/1/2011')
    insert into @table1 values (5, 'B', '1/1/2011')
    insert into @table1 values (6, 'B', '1/1/2011')


    select b.letter, b.date, count(0) AS count_ 
    from (
        select distinct letter, a.date from @table1
        cross join (select distinct date from @table1 ) a 
            ) b     
    join @table1 t1
    on t1.letter = b.letter
    and t1.date <= b.date       
    group by b.letter, b.date
    order by b.letter