Need help to get data...

[grwd]

I've never learned much SQL and I'm having some difficulty gathering the data I need for an important report.
The problem seems to be that I have two sum()s on the same field based on if another field is True or false.

I can get one value but not the other in one query.
Plus if I get the second value in a second query, I don't know how to get all the values into one table for my report.

Frankly at this point I don't care HOW my app gets the data as long as it gets to the report.

Here is a query that gets one of the Sum()s:

SELECT Customers."CustomerID", SUM(GiveFundSplit."Amount") 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 (GiveFundSplit."TaxDeductable"  = 'False') 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"

Now I need "SUM(GiveFundSplit."Amount") WHERE (GiveFundSplit."TaxDeductable" = 'True')

Please, ANY solution will be appreciated.
Thanks!
Tim
tim@tgm.org

 

[Nordlund]

Try the SQL thread...

//Nordlund

 

[svanels]

Try the sql-builder in Delphi. I suggest to make a small application, put queries for the tables needed, right-click the query component and you will have a visual tool to make your query. Play with it, and use the sql statements it generates for your final project.
My SQl is little more then "select from x where y", but with a good tool, I don't need to obtain a degree in SQL.

Steven van Els
SAvanEls@cq-link.sr

 

[donvittorio]

Tim,

a UNION should give you what you need. Beware that UNION automatically removes any duplicate rows, this can be avoided by using UNION ALL.
Note also that you only need to group by columns that you are selecting. Your sql should therefore be something like:

SELECT Customers."CustomerID", SUM(GiveFundSplit."Amount") AS SumAmount
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" = 'False') AND (GiveDonations."DonationDate" BETWEEN '01/01/2004' AND '12/31/2004') AND GiveDonations."DonationAmount" > '10.00'
GROUP BY Customers."CustomerID"

UNION ALL

SELECT Customers."CustomerID", SUM(GiveFundSplit."Amount") AS SumAmount
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"

It's not pretty and I'm sure there's a neater way of achieving this, but this gives you an easy to understand solution (I hope)

Steve