Solved

SQL query to return a new generated column based on another column

Posted on 2007-03-29
1
176 Views
Last Modified: 2010-03-20
I have an table in an Access database with three columns:

File
Date
Amount

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.
0
Comment
Question by:SETP
1 Comment
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 18815738
I think the SWITCH statement will get you there. I'm also using the Between ... And operator as it looks a little cleaner.

SELECT File,
SUM( Switch([Date] Between #01/10/2006# AND #01/11/2007#,  Amount, [Date] NOT Between #01/10/2006# AND #01/11/2006#, 0)) AS TotalOct200,
SUM( Switch([Date] Between #01/10/2005# AND #01/11/2005#,  Amount, [Date] NOT Between #01/10/2006# AND #01/11/2007#, 0)) AS TotalOct2005
 FROM Table1
 GROUP BY File
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 88
SQL join help to a thrid table 51 76
Substring() and LEFT() syntax 4 21
Linking a DMV to a database id/sql text in SQL server 2008 8 48
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now