Combining two separate SQL queries into one output

[hiigaran]

For the past year, I’ve been slowly working on a database that serves as an electronic logbook of various vehicles, adding functionality as I learn new things. Part of the database includes two queries that display related information. They both work fine, but I’d like to combine them into one query to improve readability. I’m not sure how to combine them without syntax errors being thrown up, though (Firebird).

The first query is named “Totals: Vehicles”:

SELECT
	"Vehicle Type",
	SUM("Category 1")+
	SUM("Category 2")
	AS "Total Time In Service",
	SUM("Category 3") AS "Total Events"
FROM "Data Entry"
GROUP BY "Vehicle Type"

This will output the columns “Vehicle Type”, “Total Time In Service”, and “Total Events”

The second query is named “Third Event Check”:

SELECT
	"Vehicle Type",
	DATEDIFF(DAY, "Date", CURRENT_DATE) AS "Days Since 3rd Last Event"
FROM
	(
		SELECT
			"Date",
			"Category 3",
			"Vehicle Type",
			"Event Count",
			ROW_NUMBER() OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC) AS "rn"
		FROM
			(
				SELECT
					"Date",
					"Category 3",
					"Vehicle Type",
					SUM("Category 3") OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC) AS "Event Count"
				FROM "Data Entry"
			)
		WHERE "Event Count" >= 3
	)
WHERE "rn" = 1

This will output the columns “Vehicle Type” and “Days Since 3rd Last Event”

Is there a way I can combine the two queries, so that I can output the columns “Vehicle Type”, “Total Time In Service”, “Total Events”, and “Days Since 3rd Last Event” all in one go?

[RobertG]

I would prefer this method:
Create a view from both queries.
Create a query, which connects this two views. Connect by “Vehicle Type”.

[cpb]

@hiigaran,

I think that you realise simply joining the 2 queries would not be the most efficient solution and of course you would learn nothing.

query 2 is pretty smart but could be improved by using a CASE statement:
the SUM(“Category 3”) window function will only fire when required.
the ROW_NUMBER() window function is made redundant.

when you pen a thread like this then it’s always best to include a sample database, it helps to eliminate ambiguity.
with some uncertainty this is my best guess:

select
	"Vehicle Type",
	sum(coalesce("Category 1", 0) + coalesce("Category 2", 0)) "Total Time In Service",
	sum("Category 3") "Total Events",
	max(case when "Cat3Events" = 3 then datediff(day, "Date", current_date) end) "Days Since 3rd Last Event"
--	,max(case when "Cat3Events" = 3 then "Date" end) "Date"
from
(
	select
		"Vehicle Type", "Date", "Category 1", "Category 2", "Category 3",
		case
			when "Category 3" > 0 then
				sum("Category 3") over(partition by "Vehicle Type" order by "Date" desc)
		end
		"Cat3Events"
	from
		"Data Entry"
)
group by "Vehicle Type"
order by "Vehicle Type"

[hiigaran]

There’s a good chance I’ll have to add a database file. I’ll need to remove some personal information first. As for the ambiguity, I’ll give you the full story and the actual code used so there’s no confusion/abstraction.

This database is a pilot’s logbook that I’ve been putting together over time, mostly from scratch, with a few lines of code copied from various online sources whenever I get stuck. Most electronic logbooks are spreadsheets and lack a lot of the analytical functions and queries I have made (mostly because, as we all know, spreadsheets perform poorly when there’s a lot of data to work with). Taking your code and adapting it to my query, I have the following code:

SELECT
	"Aircraft Type",
	SUM(COALESCE("ME ICUS Day", 0) +
		COALESCE("ME ICUS Night", 0) +
		COALESCE("ME Dual Day", 0)+
		COALESCE("ME Dual Night", 0)+
		COALESCE("ME Command Day", 0)+
		COALESCE("ME Command Night", 0)+
		COALESCE("SE ICUS Day", 0)+
		COALESCE("SE ICUS Night", 0)+
		COALESCE("SE Dual Day", 0)+
		COALESCE("SE Dual Night", 0)+
		COALESCE("SE Command Day", 0)+
		COALESCE("SE Command Night", 0)+
		COALESCE("Co-Pilot Day", 0)+
		COALESCE("Co-Pilot Night", 0)+
		COALESCE("Glider", 0)
	) "Total Experience",
	SUM("Instrument Flight") "Instrument Flight",
	SUM("Command PAX") "Command PAX",
	SUM("Command Landings") "Command Landings",
	MAX(CASE										--Problem Area
			WHEN									--Problem Area
				"Total Command Landings" = 3		--Problem Area
			THEN									--Problem Area
				DATEDIFF(DAY, "Date", CURRENT_DATE)	--Problem Area
		END											--Problem Area
	) "Days Since 3rd Last Landing"					--Problem Area
FROM
	(
		SELECT
			"Aircraft Type",
			"Date",
			"ME ICUS Day",
			"ME ICUS Night",
			"ME Dual Day",
			"ME Dual Night",
			"ME Command Day",
			"ME Command Night",
			"SE ICUS Day",
			"SE ICUS Night",
			"SE Dual Day",
			"SE Dual Night",
			"SE Command Day",
			"SE Command Night",
			"Co-Pilot Day",
			"Co-Pilot Night",
			"Glider",
			"Instrument Flight",
			"Command PAX",
			"Command Landings",
			CASE								--Problem Area
				WHEN							--Problem Area
					"Command Landings" > 0		--Problem Area
				THEN							--Problem Area
					SUM("Command Landings")		--Problem Area
				OVER(							--Problem Area
					PARTITION BY "Aircraft Type"--Problem Area
					ORDER BY "Date" DESC		--Problem Area
				)								--Problem Area
			END									--Problem Area
			"Total Command Landings"			--Problem Area
		FROM
			"Logbook"
	)
GROUP BY "Aircraft Type"
ORDER BY "Aircraft Type"

I’ve marked the problematic areas with comments (hopefully the formatting here works). Below, I’ve attached a screenshot of the outputs given by the current two queries on the left, and the output of the query using the code above on the right:

The code above outputs only three rows in the last column. If I compare them to the old queries, the numbers are correct, but for some reason, the rest of the numbers from the top old query did not end up in the new query. Below is the actual code for the old query:

SELECT
	"Aircraft Type",
	DATEDIFF(DAY, "Date", CURRENT_DATE) AS "Days Since 3rd Last Landing"
FROM
	(
		SELECT
			"Date",
			"Command Landings",
			"Aircraft Type",
			"Landing Count",
			ROW_NUMBER() OVER (PARTITION BY "Aircraft Type" ORDER BY "Date" DESC) AS "rn"
		FROM
			(
				SELECT
					"Date",
					"Command Landings",
					"Aircraft Type",
					SUM("Command Landings") OVER (PARTITION BY "Aircraft Type" ORDER BY "Date" DESC) AS "Landing Count"
				FROM "Logbook"
			)
		WHERE "Landing Count" >= 3
	)
WHERE "rn" = 1

Pretty much exactly what was shown in the first post, just with some of the column names changed.

So as it stands, your code is definitely a step in the right direction. Just a bit stumped on why it only works for some of the rows and not others. If you still need me to upload the database file, let me know, and I’ll work on it in the next couple of days, time permitting.

[cpb]

THIS POST REPLACES MY PREVIOUS COMMENT.
I have to admit defeat.
the clause where "Event Count" >= 3 is a road block which makes it impossible to simplify the code.
I cannot better your own efforts.
the best I can manage is to left join your very own code, sorry.
my original code will have failed because "Cat3Events" was never equal to 3 therefore the field “Days Since 3rd Last Event” returned null.

select
	t1.*, t2."Days Since 3rd Last Event"
from
(
	select
		"Vehicle Type",
		sum(coalesce("Category 1", 0) + coalesce("Category 2", 0)) "Total Time In Service",
		sum("Category 3") "Total Events"
	from "Data Entry"
	group by "Vehicle Type"
) t1
left join
(
	select
		"Vehicle Type",	datediff(day, "Date", current_date) "Days Since 3rd Last Event"
	from
	(
		select
			a.*,
			row_number() over(partition by "Vehicle Type" order by "Date" desc) r
		from
		(
			select
				"Vehicle Type", "Date",
				sum("Category 3") over(partition by "Vehicle Type" order by "Date" desc) "Event Count"
			from "Data Entry"
			where "Category 3" > 0
		) a
		where "Event Count" >= 3
	)
	where r = 1
) t2
	on t1."Vehicle Type" = t2."Vehicle Type"
order by "Vehicle Type"

[hiigaran]

No worries. My current queries still work, so no issues there. I’ll continue researching to see if I can get the new one to work. Thanks for trying