Sum a Field from Table 1 Based on Matching Criteria from Table 2

[FullMoonMadness]

I’ve searched for the solution and have been unsuccessful, hopefully someone can help me here. I have two tables with several fields, here are the pertinent ones:

Transactions (table):

• PID (primary key)
• Purchase Date
• Cost
• Location

Region (table):

• RID (primary key)
• Location
• Start Date
• End Date
• Total Cost (alias?)

I have a query based on the Region table, and in one of the fields (Total Cost as an alias?), I’d like to sum the Cost of transactions from the Transactions table that meet this criteria:

• Purchase Date >= Start Date
• Purchase Date <= to End Date
• matching Location

Can someone point me in the right direction? Thanks!

[Ratslinger]

Hello,
Using HSQLDB embedded for test.
Tables used:

SQL used:

SELECT RID,
       "rLocation",
       SUM("Cost")
FROM (SELECT *
      FROM "Transactions"
        LEFT JOIN (SELECT * FROM "Region") A
               ON ("tLocation" = "rLocation")
              AND ("PurchaseDate" BETWEEN "StartDate"
              AND "EndDate"))
GROUP BY RID,
         "rLocation"

Result:

You can add sequence and any other info.

[FullMoonMadness]

Hi Ratslinger, thank you for the response. I’m still struggling with this one. I am getting an error that says the following:

The data content could not be loaded. /home/buildslave/source/libo-core/connectivity/source/commontools/dbtools.cxx:751

Column not found: Location in statement [SELECT “Region”.“RID”, “RID”.“Location” (…and it goes on to list the whole query) The error ends with: /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:175

I think the issue is that I have a field called “Location” in both tables, so I took the steps to specify the table names in the query to try solving the above error. That didn’t work.

I then created new tables with your exact info, but used rlocation and tlocation. The Query worked perfectly. Then when I changed rLocation and tLocaiton to match as Location, the query stopped working. Even when specifying the table name first.

Here is what I entered:

SELECT "Region"."RID",
	"Region"."Location",
	SUM( "Transactions"."Cost" )
FROM ( SELECT *
	FROM "Transactions"
		LEFT JOIN ( SELECT * FROM "Region" ) "A"
		ON ( "Transactions"."Location" = "Region"."Location" )
		AND ( "Transactions"."Purchase Date" BETWEEN "Region"."Start Date"
		AND "Region"."End Date" ) )
GROUP BY "Region"."RID",
	"Region"."Location"

Do I need different field names to get this to work? Thanks!

[Ratslinger]

Hello,
Can do with just Location but will need more code. If rLocation and tLocation work, why not use that?
.
This works (although my names differ slightly from yours - I avoid spaces in names):
.
Edited - less code:

SELECT "RID",
       "Location",
       SUM("Cost")
FROM (SELECT *
      FROM "Transactions"
        LEFT JOIN (SELECT "RID", "Location" AS "Loc", "StartDate", "EndDate" FROM "Region") "A"
               ON ("Transactions"."Location" = "Loc")
              AND ("Transactions"."PurchaseDate" BETWEEN "StartDate"
              AND "EndDate"))
GROUP BY "RID",
         "Location"

.

[cpb]

@FullMoonMadness,
it’s likely that the issue stems from the SELECT * (this obviously results in duplication of the field “Location”).
.
also note that the table “Region” has been assigned the alias “A” but table “A” is then completely ignored as you go on to reference the table “Region”.
two clear errors!
.
try this code (i cannot test). note the structure and clearly defined aliases.
if you need to see periods of zero transactions then replace ‘join’ with ‘left join’.

select 
	r."Location", r."Start Date", r."End Date", sum(t."Cost") "TotalCost"
from
(
	select "Purchase Date", "Cost", "Location"
	from "Transactions"
) t
join
(
	select "Location", "Start Date", "End Date"
	from "Region"
) r
on 
	t."Location" = r."Location"
	and
	t."Purchase Date" between r."Start Date" and r."End Date"
group by "Location", "Start Date", "End Date"
order by "Location", "Start Date"