?
Solved

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

Posted on 2007-03-29
1
Medium Priority
?
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

765 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