Ask Your Question
0

combining three parametering queries and then combining their totals [closed]

asked 2017-09-29 23:29:08 +0100

pascale gravatar image

updated 2017-09-30 09:22:10 +0100

Hi, I have three queries coming from the same table

SELECT "ID", "Date", "Dévise" AS "EUR", "Net", "ExchangeR", "Net" * "ExchangeR" AS "EUR Solde" FROM "Paypal" WHERE "Dévise" = 'EUR'

This one is for Euros the others are for GBP and USD. from each there is another query which gives me the SUM at a specified date

SELECT SUM( "EUR Solde" ) FROM "soldeEUR" WHERE "Date" <= :INPUTDATE

all work great, I now wish somehow to combine the three so that I add the date only once and I can SUM the three totals. I have tried to use one query but this does not work, I would like to use a form if possible, I have tried this but I have to add the date three times and I dont know then how to sum the replies, I have saved each query as a table in view section Any suggestions welcome Regards Paul

Here is a screen shot of the query by @Ratslingerimage description

The answers should be (EUR Solde) -66.44, (GBP Solde) 6561.77 & (USD Solde) 68.84

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by pascale
close date 2017-09-30 20:21:54.522295

1 Answer

Sort by » oldest newest most voted
1

answered 2017-09-30 02:14:41 +0100

Ratslinger gravatar image

updated 2017-09-30 18:03:43 +0100

I feel obligated to again state this could be much better with some careful planning instead of piecing items as you go along. The example here is that what you require can be done in a single SQL statement instead of multiple statements creating VIEWs. Embedded HSQL (Base default) is very old and cannot handle many things. With a newer HSQL (used in split DB's and preferred method over embedded) all these interim steps are unnecessary.

With that said, there are some issues which you allude to but are really not clear on so I'll base this answer on what it seems you are stating.

It appears you have created three VIEWs - EUR, GBP & USD. With that the names used in following statement are "soldeEUR", "soldeGBP" and "soldeUSD" with the calculated field in each as "EUR Solde", "GBP Solde", "USD Solde" respectively. The following statement gets a total for each and a grand total:

Corrected 9/30/2017:

SELECT "EUR", "GBP", "USD", USD + GBP + EUR TOTAL FROM (SELECT SUM( "EUR Solde" ) "EUR" FROM "soldeEUR" WHERE "Date" <= :INPUTDATE), (SELECT SUM( "GBP Solde" ) "GBP" FROM "soldeGBP" WHERE "Date" <= :INPUTDATE), (SELECT SUM( "USD Solde" ) "USD" FROM "soldeUSD" WHERE "Date" <= :INPUTDATE)

With this you can create a form.

If this answers your question please click on the ✔ (upper left area of answer).

edit flag offensive delete link more

Comments

Hi @Ratslinger, Thank you for your interest, "I feel obligated to again state this could be much better with some careful planning instead of piecing items as you go along." I'am sorry but I am new to Base, I have read numerous tutorials including "base tutorial" "sheep dog trials" and I am learning as I go along, and I am sorry to say there is probably worse to come. I have tried your SQL Query but it gives incorrect replies. (I have added an image to the original question. Regards Paul

pascale gravatar imagepascale ( 2017-09-30 09:15:19 +0100 )edit

Hi @Ratslinger, I have looked back at your previous comments and see that you feel the underling problem is the table "Paypal", I am going to go back to the basics for this table and start again to avoid future problems, any suggestions will warmly welcome.

pascale gravatar imagepascale ( 2017-09-30 13:45:06 +0100 )edit

@pascale First, my apologies for my incorrect statement. Have corrected and re-posted the statement in my answer. It was my fault & didn't verify totals.

As far as my comments, it's all part of the learning curve. My best advise here is since this appears to be a business application, keep things as simple as possible and don't be discouraged by needing to retrace steps to do things differently.

Ratslinger gravatar imageRatslinger ( 2017-09-30 15:39:29 +0100 )edit

@Ratslinger The updated SQL works great, Thank you. There is never a need to apologize when you are helping somebody. I love learning new things, and I am always ready to listen and eager to learn, Thank you for your kind help Regards paul

pascale gravatar imagepascale ( 2017-09-30 20:21:24 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-09-29 23:29:08 +0100

Seen: 46 times

Last updated: Sep 30 '17