[Solved] Count attributes from records in joined table

[John Riviera]

GENDER.odb

(4.46 KiB) Downloaded 220 times

Hello,

I need to understand what is wrong.
There is a joined table called T_GENDER.
I want to know how many males and how many females.
Created a Request to select Males : R_GenderM and another one for females : R_GenderF.

Then this one more request called R_GenderCount, in which I select as a source R_GenderF, crit=F-> result = 7

Code: Select all

SELECT COUNT( "Gender" ) FROM "R_GenderF"

But I'd like to have in this one request, both answers of numbers of F and numbers of M. So, I imported the other request R_GenderM, as so :

Capture d’écran 2022-07-27 à 14.48.41.png (35.82 KiB) Viewed 3980 times

but then the generated code would be

Code: Select all

SELECT COUNT( "R_GenderF"."Gender" ), COUNT( "R_GenderM"."Gender" ) FROM "R_GenderF", "R_GenderM"

Is there a code to have, as an answer, 2 column, one F, one M, with numbers under ?

this code is not accepted :

Code: Select all

SELECT COUNT( "Gender" ) FROM "R_GenderF" , COUNT( "Gender" ) FROM "R_GenderM"

I thank you in advance for any help

Edit: Changed subject, was Count Request Fail  Make your post understandable by others  -- MrProgrammer, forum moderator

[charlie.it]

Try this code:

Code: Select all

SELECT (SELECT COUNT( "Gender" ) AS "F" FROM "R_GenderF") As "F", (SELECT COUNT( "Gender" ) AS "M" FROM "R_GenderM") AS "M" FROM (SELECT COUNT( "Gender" ) AS "F" FROM "R_GenderF"), (SELECT COUNT( "Gender" ) AS "M" FROM "R_GenderM")

Edit: Rather complex, to get there I used two intermediate queries and then pasted the code inside. However it works.

[John Riviera]

Edit: Rather complex, to get there I used two intermediate queries and then pasted the code inside. However it works.

It does !!
This is all the more interesting as it is impossible to open the query in design view, LibreOffice crashes. Would it be possible to explain the steps of this code ? I don't get it all.

[charlie.it]

Edit: deleted Italian names in queries

I did like this:

1. Written Query1

   Code: Select all

   SELECT COUNT( "Gender" ) AS "F" FROM "R_GenderF"

2. Written Query2

   Code: Select all

   SELECT COUNT( "Gender" ) AS "M" FROM "R_GenderM"

3. Written Query3

   Code: Select all

   SELECT "Query1"."F", "Query2"."M" FROM "Query1", "Query2"

4. Written Query4 replacing "Query1" and "Query2" with their code to Query3.

   Code: Select all

   SELECT ( SELECT COUNT( "Gender" ) AS "F" FROM "R_GenderF" ) AS "F", ( SELECT COUNT( "Gender" ) AS "M" FROM "R_GenderM" ) AS "M" FROM ( SELECT COUNT( "Gender" ) AS "F" FROM "R_GenderF" ), ( SELECT COUNT( "Gender" ) AS "M" FROM "R_GenderM" 

See attached file.
It works fine in Libre Office 7.2.6.2 , macOS 10.13.6 (usually I don't use Open office with Base).

[John Riviera]

See attached file.
It works fine in Libre Office 7.2.6.2 , macOS 10.13.6 (usually I don't use Open office with Base).

This is damn clever, well done. Bravo !

[F3K Total]

Hello,
why so complicated, using three queries?
Try this:

Code: Select all

SELECT "Gender", COUNT ("Gender") "#" FROM "T_GENDER" Group by "Gender"

...also working with a third, fourth gender...see attachment.

[charlie.it]

Nice your solution, but @John Riviera wanted 2 columns:

Esiste un codice per avere, come risposta, 2 colonne, una F, una M, con numeri sotto ?

[eremmel]

@charly.it, @John Riviera,
The query calculates now 4x a count. The SELECT-clause is ignoring the results of the FROM-clause. With oracle you might replace the complete FROM-clause with 'FROM DUAL' to indicate that you want only one record, to carry the values in the select. So I propose this solution that takes the result of the FROM-clause and present it in the SELECT-clause:

Code: Select all

SELECT "TF"."F", "TM"."M" 
FROM ( SELECT COUNT( "Gender" ) AS "F" FROM "R_GenderF" ) as "TF"
   , ( SELECT COUNT( "Gender" ) AS "M" FROM "R_GenderM" ) AS "TM"

As long as each derived table in the FROM-clause returns one record you are save, but without any relationship between both derived tables, you end up with an implicit CROSS JOIN. Just for fun another solution without CROSS JOIN:

Code: Select all

SELECT MAX("X"."F") as "F", MAX("X"."M" ) as "M"
FROM (
  SELECT COUNT( "Gender" ) AS "F", 0 as "M"
  FROM "R_GenderF"
UNION ALL
  SELECT 0, COUNT( "Gender" )
  FROM "R_GenderM" 
) AS "X"

P.S.
From a table-design perspective it makes more sense to have one table that contains both genders. Put variation of data in columns of tables and not in table names. Having both counts returned as one records would still be an interesting question though. The current solution would get more complicated when a new gender is needed like 'X'