Queries in LibreOffice Base...... (SOLVED)

[tenfoot]

Using Linux Mint v21.1 Mate and LibreOffice v7.3.7.2 Base. I have created a database with one Table - Reactors Table 1. It contains fields for Name, Gender, Location, First, Last. I have created queries to extract information and can obtain the result I need but not in the form I want. Let me give you an example.

The field Gender identifies the gender of the Reactor and is shown by a single letter, i.e. M or F

My SQL query to identity all the Reactors who are Female is

SELECT "Gender" FROM " Reactors - Table1" WHERE "Gender" = 'F' - and it succeeds in listing all of them, but I have to scroll down until I get to the end (471) and this means having to use Alt+Scroll as I run out of room on the monitor.

I have read countless tutorials on YouTube about creating queries, but have yet to find one which will just give me the total (471)

Is there any member on the Forum familiar with LibreOffice Base able to point me in the right direction, please?

[RIH]

The easiest way is...
You create a query that just gives you totals - rather than details.
To do that, under Function of a selected field you just say Group.

ksnip(2).png (10.05 KiB) Viewed 266 times

so, for example, you create a query that Groups by Gender & Counts (really any of the other fields) Name
So the output will be ....

ksnip.png (3.67 KiB) Viewed 266 times

Query as SQL...

SELECT "Gender", COUNT( "Name" ) FROM "Table1" GROUP BY "Gender"

[RIH]

Just to add...
If you only wanted to count "F's" then in Criteria (after Function = Groups) you just put the letter F.
However, for most group queries it is probably better not to specify as ....
1. You will need to create another query for "M's".
2. You will catch any faulty data by not specifying that you will miss by specifying.

[tenfoot]

Hi! Thank you RIH for your very prompt reply.

The commands worked perfectly. I won't bother creating for all genders, but just stick with what I've got.

An explanation is probably necessary. The database is of those who post reaction videos on YouTube for a particular artist I follow. The field "Gender" reflects whether the person creating the reaction video is alone (Male or Female) or there are other people in the video. So I end up with F, M, F/M. M/M, M/F, and F/F.

I also have a field "Location", which is the place the channel owner declares in the About section of his YouTube channel, and I have adapted the "Gender" code to "Location" and it works, in that it lists all the locations (i.e. countries") but they are not in alphabetical order. Obviously I am missing something and any help you can give will be very much appreciated

[RIH]

Sure, in table view you just click on Sort & pick ascending.

In SQL that looks like..

SELECT "Gender", COUNT( "Name" ) FROM "Table1" GROUP BY "Gender" ORDER BY "Gender" ASC

ASC for ascending & DESC for descending are your only choices..

[tenfoot]

Hi RIH! Thank you, again, for providing the solution to my problem.

Although not new to Base. I have only used it in the creation of this one Table, and queries are new to me. That you have willingly stepped in to help me gives me encouragement to create more Tables, e.g. the songs/performances the Reactor is featuring. So I will have to learn how to link them and link., etc. So much to do and at age 91 so little time to achieve what I want to do.

I have marked this subject as solved.

[RIH]

You are welcome, I am glad you have got what you wanted.

Yes, the key to expanding your database is to find the linking fields between each table.
It is far more efficient to have a number of small tables than 1 large one..

If you get stuck in the future then just raise another thread.
I am certainly not an expert on Base but I did spend quite a bit of my working life creating user databases in Access & the principles are very similar..

[tenfoot]

Hi! My only other experience with databases was with Paradox, over 30 years ago, and that was only to create a simple database. By the time I came to LibreOffice Base, I'd forgotten everything I learned.

As has often been said "Watch this space", or to be more accurate, look for another post from me seeking help because, inevitably I'll lose my way.