Hi I have been searching all over for an answer to this. Simply put I have a table (members) with fields (branches, gender) and a heap of others what I need is through a SQL query to get the following for a certain date. Here is a pic(capture1) of what I mean. I need a list of the branches with the male/female numbers and totals. I have managed half of it but cannot get the males/females to display together. I hope someone can help asap. Thanks so much, in the meantime I'll keep trying...Regards

 

We can't help you with a SQL query without knowing more about the relational structure of your solution. What fields do you have in what tables that this is drawing from? What have you tried so far, and what result did you get with that?

Automatic message

This topic has been moved from "Managing Scripts" to "FQL or FileMaker Query Language".

We can't help you with a SQL query without knowing more about the relational structure of your solution. What fields do you have in what tables that this is drawing from? What have you tried so far, and what result did you get with that?

So far, The fields are all from one table (members13). The data is being pulled from the fields memnum (membership number), Branch (name of the branch), Gender (male/female) and the current month from a variable input. I have tried using :-

SELECT Branch, COUNT(*)

FROM members13

GROUP BY Branch

which gives me a list of the branches and the total members for each branch.

have also tried:-

SELECT Branch, COUNT(*)

FROM members13

WHERE Gender = 'Male'

GROUP BY Branch

which gives me a list of the branches with all the male totals.

I need this in 1 query (if possible) or the easiest way to get the results below:

BRANCH - TOTAL MALES - TOTAL FEMALES - TOTAL

branch1               20                         18                  38

branch2               44                          21                 65

etc. 

Hope this helps

You can do it in query, but not with one SELECT.  So for each gender you need to embed one SELECT...WHERE and one "master" SELECT around the whole thing to include the branch.  So your query will have three SELECTs in it.

actually this should work:

 

 

"SELECT 

branch,

COUNT(*) AS totalCount,

SUM( case when gender = 'F' then 1 else 0 end) as females,

SUM( case when gender = 'M' then 1 else 0 end) as males 

FROM theData 

GROUP BY branch"

Nice query, Wim! Yes that should work really well.