Is Possible 2 Sums from Same Field in 1 query?

[grwd]

I have a Customer table, a Donations table and a FundSplit table. Relationship: (Customer <->> Donations <->> FundSplit)

Customer Fields: CustID, Name, ... (address fields)
Donations Fields: CustID, DonationID, Date, ...
FundSplit Fields: DonationID, Amount, TaxDed, ...

What I am after is a query (or queries) that will yield a record with the following fields:
CustID, Name, (the other address fields), DeductableAmount, NonDeductableAmount
Where Date > 12/31/2003 and <= 12/31/2004

I'm learning, but seem to be having difficulty with this one.
Can someone help me here?

Thank you!
grwd
tim@tgm.org

 

[Golom]

Something like

Select C.CustID, C.CustName, C.AddrFields, 
       SUM(F.Amount) As DeductableAmount, 
       SUM(F.TaxDed) As NonDeductableAmount

From (Customer As C INNER JOIN Donations As D
     ON C.CustID = D.CustID)
     INNER JOIN FundSplit As F 
     ON F.DonationID = D.DonationID

Where D.[Date] BETWEEN #12/31/2003# AND #12/31/2004#

Group By C.CustID, C.CustName, C.AddrFields

I may not be summing the right fields from FundSplit but those are the only ones that you showed.

 

[grwd]

Golom,
Thank you for your response.
Actually the two sums that I need to get are both Sum(Amount).
One where TaxDed = True and the other where TaxDed = False.

[b]Select C.CustID, C.CustName, C.AddrFields,
       SUM(F.Amount) As DeductableAmount[/b]
       [COLOR=green]// SUM(F.TaxDed) As NonDeductableAmount[/color]

[b]From (Customer As C INNER JOIN Donations As D
     ON C.CustID = D.CustID)
     INNER JOIN FundSplit As F
     ON F.DonationID = D.DonationID

Where D.[Date] BETWEEN #12/31/2003# AND #12/31/2004#

Group By C.CustID, C.CustName, C.AddrFields[/b]

The BOLD code should provide the Deductable Amount, but I ALSO need the NON-Deductable Amount. Preferably in one query. If NOT, then in multiple queries.

Also what are the #'s around the date for?

Thank you,
Tim
tim@tgm.org

 

[Golom]

OK. Give this a shot

Select C.CustID, C.CustName, C.AddrFields, 
       SUM(IIF(F.TaxDed,F.Amount,0)) As DeductableAmount, 
       SUM(IIF(NOT F.TaxDed,F.Amount,0))  As NonDeductableAmount

From (Customer As C INNER JOIN Donations As D
     ON C.CustID = D.CustID)
     INNER JOIN FundSplit As F 
     ON F.DonationID = D.DonationID

Where D.[Date] BETWEEN #12/31/2003# AND #12/31/2004#

Group By C.CustID, C.CustName, C.AddrFields

 

[r937]

the octothorpes (or hash marks) are the date string delimiters for microsoft access

i don't know why golom thought this was an access problem

perhaps it was the True/False values for your TaxDed column

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PHV]

Anyway I'd replace this:
Where D.[Date] BETWEEN #12/31/2003# AND #12/31/2004#
By either this:
Where D.[Date] BETWEEN #01/01/2004# AND #12/31/2004#
Or this:
Where Year(D.[Date]) = 2004

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

Rudy
Golom thought this was an Access problem because Golom doesn't read very well and/or forgets which forum he's in.
Sorry about that.

The correct delimiters in ANSI SQL are of course, single quotes ... not # characters.

 

[r937]

hey, no prob, it's perfectly okay to give solutions in the dbms dialect of the question (at least, it seems so to me)

and it's often easier to do so than go through the hassle of asking the person to post the question elsewhere

i'll always give standard sql syntax if the particular dbms dialect includes it, but otherwise, i too would give access syntax if it is obvious that it is an access question



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[SQLSister]

Isn't IIF Access syntax as well? Shouldn't we be using the Case function instead?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[r937]

yes it is, and yes we should, except in access, which doesn't understand CASE



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[grwd]

Hey guys,
Thank for your help and comments.
Unfortunately, I'm programming in Delphi using Paradox tables. (Sorry I didn't say so before.
Evidently, It took the "Between" code but doesn't seem to like the IIF code. (Error: Capibility not supported!)
I know that ties one hand behind your back, but would you mind trying again to provide a solution.

Here is an actual working SQL query that gets me ONE of the two sums:

SELECT Customers."CustomerID", Customers."Name", Customers."Street", Customers."Suburb", Customers."City", Customers."ZipCode", Customers."State", Customers."Country", SUM(GiveFundSplit."Amount") AS Deductable
FROM "Customers.DB" Customers
	INNER JOIN "GiveDonations.DB" GiveDonations
	ON (Customers."CustomerID" = GiveDonations."DonorID")
	INNER JOIN "GiveFundSplit.DB" GiveFundSplit
	ON (GiveDonations."DonationID" = GiveFundSplit."ContributionID")

WHERE (GiveFundSplit."TaxDeductable"  = 'True') AND (GiveDonations."DonationDate" BETWEEN '01/01/2004' AND '12/31/2004') AND GiveDonations."DonationAmount" > '10.00'
GROUP BY Customers."CustomerID", Customers."Name", Customers."Street", Customers."Suburb", Customers."City", Customers."ZipCode", Customers."State", Customers."Country"
ORDER BY Customers."CustomerID"

I need to mail out our year end Charitable Donations Recipts soon and the IRS now requires us to include Total Donations, Deductable Amount and Non-Deductable Amount (if they received anything tangible (like a book) in return.

So I need this data in one table or query for the report:
John Smith
123 Some St.
Mytowne, ST, 01077
USA

Total Gifts: 100.00
Non-Deductable: 20.00
Deductable: 80.00

 

[SQLSister]

Does Paradox have the Case statement? Check your documentation to see. Something like this might work:

SELECT Customers."CustomerID", Customers."Name", Customers."Street", Customers."Suburb", 
Customers."City", Customers."ZipCode", Customers."State", Customers."Country", 
SUM(case GiveFundSplit."TaxDeductable" when 'true' then GiveFundSplit."Amount" else 0 end) AS Deductable,
SUM(case GiveFundSplit."TaxDeductable" when 'false' then GiveFundSplit."Amount" else 0 end) AS NonDeductable
FROM "Customers.DB" Customers
    INNER JOIN "GiveDonations.DB" GiveDonations
    ON (Customers."CustomerID" = GiveDonations."DonorID")
    INNER JOIN "GiveFundSplit.DB" GiveFundSplit
    ON (GiveDonations."DonationID" = GiveFundSplit."ContributionID")

WHERE (GiveDonations."DonationDate" BETWEEN '01/01/2004' AND '12/31/2004') AND GiveDonations."DonationAmount" > '10.00'
GROUP BY Customers."CustomerID", Customers."Name", Customers."Street", Customers."Suburb", Customers."City", Customers."ZipCode", Customers."State", Customers."Country"
ORDER BY Customers."CustomerID"

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[PHV]

If in Paradox the booleans are stored the standard way (ie True=-1 and False=0):
-SUM(GiveFundSplit."Amount" * GiveFundSplit."TaxDeductable") AS Deductable,
SUM(GiveFundSplit."Amount" * (1 + GiveFundSplit."TaxDeductable")) AS NonDeductable,
SUM(GiveFundSplit."Amount") AS TotalGifts

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grwd]

When Paradox came out it had a "Query by Example" engine built in, and only provided the most basic SQL capibilities. Evidently no IIF, no IF no CASE etc...
(FYI: Booleans = True or False in Paradox.)

I really appreciate your assistance.
But since I couldn't gather what I needed using SQL I had to go ahead and resort to the raw coding of nested while-loops to gather the needed data.
Much slower and less elegant but it should get the correct results.

I'm sure I'll be back for my future query needs.