WHERE syntax question

[UncleRobin]

Version: 7.2.1.2 (x64) / LibreOffice Community
Build ID: 87b77fad49947c1441b67c559c339af8f3517e22
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-NZ (en_NZ); UI: en-GB
Calc: CL

Thanks to the tip to update my LO to the current release version, I’ve made progress in building the query I was after. The screenshot shows how far I’ve got. I have successfully queried my table for the average score of Dramas I have completed, grouped by Network.

What I would like to do is further limit the query to only include those Networks whose ‘completed’ count is >=6, to eliminate outliers that might have very high or very low scores from only 1 or 2 Dramas watched.
I think I can build another query from this query, and assume that I need to somehow say something like “WHERE COUNT of ‘completed’ >=6”, but the correct syntax is eluding me. Rather than simply ask someone to write the SQL for me (which would be super convenient for me, of course), I might learn more if told (a) whether or not I’m on the right track and (b) where I can find syntax examples that could show me how to write the query I want. Of course, if I’m fundamentally wrong about how to achieve the desired result, then a correct sample code would be a boon

[Ratslinger]

Hello,

Do not have a place with samples (fairly certain you are using HSQLDB embedded database) but will give an example.

Starting with this table:

want to know all employees with unique last name. We can get the count:

The problem is you cannot use the count result until it has been generated. You can use a select statement as one of the tables and then use the count:

Note that I have used SQL view for all this and not the Design view presented. There are some things you cannot do unless in SQL mode. I basically use this mode most of the time.

Also, the "A" at the end of the count select statement is the name given to that result set. You can the refer to those selected items uniquely as was done with "A"."LastName"

[UncleRobin]

Thank you! Thanks especially for the SQL - I want to get into the habit of using SQL rather than Design View, so that I can learn more about how it works, and to be able to move beyond HSQLDB if I want to at some future time. I’ll try to adapt the SQL you provided and see if I can get the result right

[UncleRobin]

Trying this one step at a time, I entered this SQL code,

SELECT Count(“NETWORK”) “cCount”,“NETWORK” from “MasterlistGenre” Group by "NETWORK”

This seemed like a good start,so I saved the resulting Query as “Completed’.

Then I tried modifying the second Select Count statement to fit, and here is where my profound ignorance of SQL is really exposed.

I tried the following

Select “NETWORK” From “Completed”,
(Select Count (“NETWORK”) “cCount” , “NETWORK” from “Completed” Group by “NETWORK”)“A” Where “cCount” >= 5 And “Completed”.“NETWORK” = “A”."NETWORK”

The result was this:

This gave the correct total count, omitting those Networks with counts <5, but didn’t group them.

So I looked again at your example, and modified the query to something that I though fitted more closely

---

Select “NETWORK” From “Completed”,
(Select Count (“NETWORK”) “cCount” , “NETWORK” from “MasterlistGenre” Group by “NETWORK”)“A” Where “cCount” >= 5 And “Completed”.“NETWORK” = “A”.“NETWORK”

This returned 355 records, a number I couldn’t make sense of as it was neither the total of all records (393) nor the number of records with counts <5 (348), so I tried again with

---

Select “NETWORK” From “MasterlistGenre”,
(Select Count (“NETWORK”) “cCount” , “NETWORK” from “Completed” Group by “NETWORK”)“A” Where “cCount” >= 5 And “MasterlistGenre”.“NETWORK” = “A”.“NETWORK”

And that returned an ungrouped list of all entries by network regardless of score and including those not completed.

I’ve included all my failed attempts because I’m very keen to understand what is it that I have failed to grasp. I want to get my head around the SQL so that I can (a) proceed to the next step of querying the average scores for the Networks that meet my desired selection criteria and (b) eventually write more complex queries.
I know that understand SQL is key to moving past the constraints of the Design View (or QBE as my first DB tutor called it decades ago), but it’s clear that I have completely failed to grasp this simple example. I’m hoping you can cut through my mental fog, thank you!

[Ratslinger]

@UncleRobin

I have completely failed to grasp this simple example.

I think you simply overlooked the table names. In my answer, the first query was only to show the result. That was not used in the second example. In yours, you used the first in the second and caused problems. Try:

Select “NETWORK” From “MasterlistGenre”,
(Select Count (“NETWORK”) “cCount” , “NETWORK” from “MasterlistGenre”
Group by “NETWORK”)“A” Where “cCount” >= 5 And
“MasterlistGenre”.“NETWORK” = “A”.“NETWORK”

The first statement (with Count) is embedded in the second statement.

[UncleRobin]

@Ratslinger

Select “NETWORK” From “MasterlistGenre”,
(Select Count (“NETWORK”) “cCount” , “NETWORK” from “MasterlistGenre”
Group by “NETWORK”)“A” Where “cCount” >= 5 And
“MasterlistGenre”.“NETWORK” = “A”.“NETWORK”

I copied and pasted, but for some reason Base inserted extra quote marks around the pre-existing ones, and that generated SQL syntax errors, so I typed as follows:

SELECT “NETWORK” FROM “MasterlistGenre”,
(SELECT Count (“NETWORK”) “cCount” , “NETWORK” from “MasterlistGenre” Group by “NETWORK”)“A” WHERE “cCount” >=5 And “MasterlistGenre”.“NETWORK” = “A”.“NETWORK”

This is what that returns:

Again, the number of records returned is confusing. The MasterlistGenre table has a total of 587 records. 74 are “TYPE” = ‘film’, 119 are “TYPE” =‘drama’ and “STATUS” = ‘dropped’, 1 is “TYPE” = ‘drama’ and “STATUS” =‘watching’, and 393 are “TYPE” =‘drama’ and STATUS = ‘completed’. Since I’m trying to select “TYPE” = ‘!film’ “STATUS” = ‘completed’ From the table, I’m at a loss to see how 465 records are being returned.

[Ratslinger]

@UncleRobin

Again, the number of records returned is confusing

Yeah. That is because it lists every occurrence of qualified records.

Change the starting line from:

SELECT “NETWORK”

to

SELECT Distinct(“NETWORK”)

Replace the quotes here. This site is replacing regular quotes with curly quotes & that doesn’t work in SQL.

[UncleRobin]

That worked well, thanks! Now the query returns 24 Networks from which I have completed watching at least 5 Dramas.

The hours poring over the code you kindly provided, prompted me to try again with my earlier query, and I found that could create the query I wanted in Design View.

In the above screenshot, when I edited the Design View, I had put the ’ Criterion >4’ in the first column, but Base helpfully corrected it for me to be as shown, with the SQL as follows:

SELECT COUNT( “MasterlistGenre”.* ), “MasterlistGenre”.“NETWORK”, AVG( “MasterlistGenre”.“SCORE” ) FROM “MasterlistGenre” WHERE “STATUS” = ‘completed’ AND “TYPE” != ‘film’ GROUP BY “NETWORK” HAVING ( ( COUNT( * ) > 4 ) )

And I finally had what I was after!

That “HAVING” criterion could be really helpful, so thanks again for helping point me to SQL, and for patiently making sense of my suboptimally clear questions.