SQL query to return a new generated column based on another column
Posted on 2007-03-29
I have an table in an Access database with three columns:
If I would like to retrieve the sum of the amounts for each file, from 1st of October to 1st of November 2006, I would write an SQL query like this:
SELECT File, SUM(Amount) AS Total FROM Table1 WHERE ([Date] >= #01/10/2006#) AND ([Date] < #01/11/2007) GROUP BY File
Now, what if I wanted to return three columns instead of two. The first two columns would be lthe same as above (File and Total), and the third column would be another total - the sum of all Amounts from 1st of October 2005 to 1st November 2005.