Can I do this in one query?

[UncleRobin]

In my drama-watching database I have 3 queries that tell me

1. How many I’ve Started
2. How many I’ve completed
3. How many I’ve dropped
   
   

The SQL for them is straightforward,

SELECT "tCountries"."COUNTRY", COUNT( "tDramas"."COUNTRY" ) AS "Started" FROM "tDramas", "tCountries", "tSTATUS" WHERE "tDramas"."COUNTRY" = "tCountries"."Country-ID" AND "tDramas"."STATUS" = "tSTATUS"."Status-ID" AND "tDramas"."Times Watched" > 0 AND "tDramas"."STATUS" > 2 GROUP BY "tCountries"."COUNTRY" ORDER BY "Completed" DESC

changing the criterion value for “Status” as required, but I would like to be able to run a query that added each country’s completed and dropped then gave me a drop percentage. At the moment, I do this manually, e.g. for South Korea 81/354 = 22.89%

Can I do this in one query, or will I need to build a query from the results of the other queries? I have built a couple of queries with simple calculations based on totals from other queries, but nothing involving grouped subtotals as in these queries. That’s why I’m a bit stuck as to how to achieve this. Thanks in advance for any and all advice

[cpb]

based on the minimal information provided it’s impossible to say.
an example odb would be ideal.
at the very least post the SQL from all 3 queries.

[RobertG]

You have created 3 Queries. Do something like this:

SELECT "a".*, "b".*, "c".* FROM
(Select …) AS "a",
(Select …) AS "b",
(Select …) AS "c"
WHERE "a"."COUNTRY" = "b"."COUNTRY" AND "b"."COUNTRY" = "c"."COUNTRY"

So you get all together in one query. You could also calculate with values from the different queries, which are now “a”, “b” and “c” of the new query.
Only the ORDER BY statement has to be dropped in the queries. You could order it in the main query as you want.

[UncleRobin]

Thanks again RobertG! Not just for the solution but for confirming my impression that this was the sort of approach I would need to take

[UncleRobin]

Thanks for the suggestion. Here is a copy of the odb, as requested.
AsianEnt1.odb (203.5 KB)

[cpb]

I did not attempt to answer your initial post because of uncertainty over table design, relationships, value of STATUS etc.
.
having now looked at your db I can see that it is feasible to use your 3 existing queries as proposed by RobertG, however although doing that does solve your issue it’s extremely inefficient.
.
you may prefer a single query using conditional aggregation which is able to produce a more informative result set much more efficiently.
.
you choose which to use, having a choice can only be beneficial.
.
it’s easy to add or delete fields/calculations according to preference.
.
this code requires direct mode:

1. Create Query in SQL View
2. paste the code
3. menu:>Edit>Run SQL command directly
4. hit F5

select
	c.country,
	d."started",
	d."completed",
	d."dropped",
	d."watching",
	d."on hold",
	100.00 * d."completed" / d."started" "completed%",
	100.00 * d."dropped" / d."started" "dropped%",
	d."score" / d."completed" "AveCompletedScore"
from
(
	select
		country,
		sum(case when status < 2 and "Times Watched" > 0 then 1 else 0 end) "started",
		sum(case when STATUS = 1 then 1 else 0 end) "completed",
		sum(case when STATUS = 0 then 1 else 0 end) "dropped",
		sum(case when STATUS = 2 then 1 else 0 end) "watching",
		sum(case when STATUS = 3 then 1 else 0 end) "on hold",
		sum(case when STATUS = 1 then score else 0 end) "score"
	from
		(select country, status, "Times Watched", score from "tDramas")
	group by country
) d
join
	"tCountries" c
	on d.country = c."Country-ID"
order by country

[UncleRobin]

That’s wonderful, thanks. Since I made acopy of the odb to paste her, I can try both methods, one in each database. Thank you both, especially for the learning oppoortunities both methods present. My the LO Bas userguide open, I’m confident that I will learn a lot by studying both methods and trying them out

[UncleRobin]

I have another question on this query, if I may:

I would like to run this query limiting it to a specified year, which I thought would be

WHERE "tDramas"."Year 1st Watched" = 2021

(example date value, I want to understand the syntax before adding a user entry parameter.)

I’ve just spent a couple of hours reading the relevant sections of the Base manual, videos on how to set query parameters and the code you supplied, and am still stuck with this question - where do I put “Where”?

I think the nesting has got me muddled. I’ve tried placing it after

from
		(select country, status, "Times Watched", score from "tDramas")
	

I even tried placing it inside the brackets, after score from (which failed as I expected it would). What am I missing here?

[cpb]

to restrict output to a specific “Year 1st Watched” replace this

(select country, status, "Times Watched", score from "tDramas")

with this

(select country, status, "Times Watched", score from "tDramas" where "Year 1st Watched" = 2021)

one of these may be useful:

where "Year 1st Watched" in(2021, 2023)
where "Year 1st Watched" between 2021 and 2023

[UncleRobin]

Thank you! This is very reassuring as it means the problem must have been a typo. Your fix worked, and it is the very first thing I tried before asking here. It generated syntax errors for me, so I must have mistype something. I’m glad because it means that I actually HAD understood where to put the where, and was simply let down by my poor hand-eye coordination and motor skills. Very much appreciated!