complex SQL query with SQLite

Question

I need to make a complex query, and I need help. Below is an example of what do i have:

id   |   Date   |  Validity

48   | 6-1-2009 |  notFound   
47   | 6-1-2009 |  valid   
46   | 6-1-2009 |  valid    
45   | 3-1-2009 |  invalid   
44   | 3-1-2009 |  invalid   
42   | 4-1-2009 |  notFound 
41   | 4-1-2009 |  notFound
48   | 4-1-2009 |  valid 

[Here come the SQL.]

And the query result would look like:

   Date   |  valid   |  invalid   |    notFound

 3-1-2009 |   0      |     2      |       0
 4-1-2009 |   1      |     2      |       2
 6-1-2009 |   3      |     2      |       3

Can i do this only in SQLite? Can someone help me? I'm working in Python.

I need this to generate a line graph out of it. The example would be: line chart!

Answer 1

I believe this should do it:

SELECT Date,
SUM(Validity = "valid") AS valid,
SUM(Validity = "invalid") AS invalid,
SUM(Validity = "notFound") AS notFound
FROM table_name
GROUP BY Date

The SUM function will tally the total count of the various Validity types. The '=' function returns 0 when false and 1 when true, which is what allows this to work.

** EDIT **

I just realized that this isn't quite what you want, as you're looking for an overall aggregate at each date that also includes data from the previous date. This can be done fairly easily in python.

valid_sum = 0
invalid_sum = 0
notfound_sum = 0
for r in cursor.fetchall():
    date = r[0]
    valid_sum += int(r[1])
    invalid_sum += int(r[2])
    notfound_sum += int(r[3])
    # print aggregate data for this date

Answer 2

Your Date column cannot be properly sorted because it does not begin with the most significant field, the year.

Assuming that you change the date to have a proper yyyy-mm-dd format, you could use something like this:

SELECT A.Date,
       (SELECT COUNT(*) FROM MyTable AS B
        WHERE B.Date <= A.Date
          AND B.Validity = 'valid') AS valid,
       (SELECT COUNT(*) FROM MyTable AS B
        WHERE B.Date <= A.Date
          AND B.Validity = 'invalid') AS invalid,
       (SELECT COUNT(*) FROM MyTable AS B
        WHERE B.Date <= A.Date
          AND B.Validity = 'notFound') AS notFound
FROM (SELECT DISTINCT Date FROM MyTable
      ORDER BY Date) AS A