This will rely heavily on the magic of a "pivot" technique. Here are some more examples in JSE to compare/contrast with https://joomla.stackexchange.com/questions/tagged/pivot.
Basically, a WHERE clause will disqualify rows before grouping occurs. In this case, you only want to focus on data in the scope of FormId 14, so this should go in your WHERE clause.
Then all data needs to remain "attached/related" to the single person that submitted the form -- so the FormId 14 rows need to be grouped by SubmissionId.
Then you need to disqualify SubmissionIds that do not have an empty string as the EXITDATE value. This is where some people panic and/or get confused. Deep breath, it's not impossible to comprehend.
After GROUP BY is called, all column data that is NOT listed in the GROUP BY declaration becomes "Aggregate Data". In other words, the grouped column(s) can be used directly in a SELECT clause because logically, there can only be 1 value for that column. As for the remaining data, think of those rows as being "clusters/clouds/masses" of data. There are dedicated functions to help you handle these non-singular portions of data. https://www.mysqltutorial.org/mysql-aggregate-functions.aspx
The HAVING clause behaves similarly to the WHERE clause, but it only begins filtering AFTER the grouping is done. Next, the IF() condition is going to be executed on every "row" of data. Because there is no way to isolate the position of a known row in the aggregate data, every row's FieldName value must be checked to ensure that the FieldValue is actually the sought value. All rows in the aggregate will be evaluated as NULL (within the scope of the conditional expression) and ONLY the EXITDATE value will be non-NULL. MAX() will then pick the greatest value of all of evaluated rows and return either an empty string or a date string. If the isolated value is equal to the empty string, then the SubmissionId qualifies for the SELECT clause.
In the SELECT clause, the same aggregate-filtering technique is used again to determine which values should be delivered in the result set.
TL;DR... Enough academia, let's get practical!
To isolate the individual people, their gender, and their current shelter, here is a simple pivot query that will exclude people whom have exited the shelter.
SELECT
SubmissionId,
MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
MAX(IF(FieldName = 'GENDER', FieldValue, NULL)) AS Gender
FROM o2pe0_rsform_submission_values
WHERE FormId = 14
GROUP BY SubmissionId
HAVING MAX(IF(FieldName = 'EXITDATE', FieldValue, NULL)) = ''
Output: (SubmissionId is only listed in the SELECT to demonstrate the relativity of data)
| SubmissionId | Shelter | Gender |
| ------------ | ---------- | ------ |
| 46978 | Mossel Bay | Male |
| 46979 | Mossel Bay | Male |
| 46980 | Mossel Bay | Female |
| 47013 | Swartland | Male |
| 47014 | Swartland | Male |
| 47015 | Swartland | Male |
| 47016 | Bellville | Male |
| 47017 | Bellville | Male |
To de-identify the data, group by Shelter and count the Genders, I think using the above pivot as a subquery is a sensible choice (I have not spent enough time thinking about alternatives).
SELECT
Shelter,
Gender,
COUNT(*) AS People
FROM (
SELECT
MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
MAX(IF(FieldName = 'GENDER', FieldValue, NULL)) AS Gender
FROM o2pe0_rsform_submission_values
WHERE FormId = 14
GROUP BY SubmissionId
HAVING MAX(IF(FieldName = 'EXITDATE', FieldValue, NULL)) = ''
) DerivedTable
GROUP BY Shelter, Gender
ORDER BY People DESC, Shelter, Gender
Output:
| Shelter | Gender | People |
| ---------- | ------ | ------ |
| Swartland | Male | 3 |
| Bellville | Male | 2 |
| Mossel Bay | Male | 2 |
| Mossel Bay | Female | 1 |
To de-identify the data, group by Shelter and count the two listed Genders individually, assign (hardcode) separate Male and Female columns in the derived pivot table before summing the column values in the parent SELECT.
SELECT
Shelter,
SUM(Females) AS Females,
SUM(Males) AS Males
FROM (
SELECT
MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
MAX(IF(FieldName = 'GENDER' AND FieldValue = 'Male', 1, 0)) AS Males,
MAX(IF(FieldName = 'GENDER' AND FieldValue = 'Female', 1, 0)) AS Females
FROM o2pe0_rsform_submission_values
WHERE FormId = 14
GROUP BY SubmissionId
HAVING MAX(IF(FieldName = 'EXITDATE', FieldValue, NULL)) = ''
) DerivedTable
GROUP BY Shelter
ORDER BY Shelter, Females, Males
Output:
| Shelter | Females | Males |
| ---------- | ------- | ----- |
| Bellville | 0 | 2 |
| Mossel Bay | 1 | 2 |
| Swartland | 0 | 3 |
I trust this will be enough to get you going in your project. If you need more examples, I have a few Stack Overflow pivot answers too. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. I don't want to get roped into doing too much free work, but if you get stuck again, feel free to ask another question and I'll be sure to have a look.
P.S. If you continue to research and discover CASE blocks, these have the same behavior as IF() blocks in sql. Though the syntax differs slightly, feel free to use IF and CASE interchangeably.
Okay, okay, one final query (but no more edits on this post)...
Demo
SELECT
Shelter,
SUM(IF(MONTH(Entry) = MONTH(NOW()), Female, 0)) AS FemalesInLastMonth,
SUM(IF(MONTH(Entry) = MONTH(NOW()), Male, 0)) AS MalesInLastMonth,
SUM(Female) AS FemalesInLast3Months,
SUM(Male) AS MalesInLast3Months
FROM (
SELECT
MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
STR_TO_DATE(MAX(IF(FieldName = 'ENTRY', FieldValue, '01-01-1901')), '%d/%m/%Y') AS Entry,
MAX(IF(FieldName = 'GENDER' AND FieldValue = 'Male', 1, 0)) AS Male,
MAX(IF(FieldName = 'GENDER' AND FieldValue = 'Female', 1, 0)) AS Female
FROM o2pe0_rsform_submission_values
WHERE FormId = 14
GROUP BY SubmissionId
HAVING MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) = 'Mossel Bay'
AND STR_TO_DATE(MAX(IF(FieldName = 'ENTRY', FieldValue, '01-01-1901')), '%d/%m/%Y') >= DATE(NOW() - INTERVAL 3 MONTH)
AND MAX(IF(FieldName = 'EXITDATE', FieldValue, NULL)) = ''
) AS DerivedTable
GROUP BY Shelter
Result Set: (not very exciting with limited input data)
| Shelter | FemalesInLastMonth | MalesInLastMonth | FemalesInLast3Months | MalesInLast3Months |
| ---------- | ------------------ | ---------------- | -------------------- | ------------------ |
| Mossel Bay | 0 | 0 | 0 | 1 |
Note there will be an avoidable expense to querying on your d/m/Y formatted date strings, because employing MySQL's date functions will require special preparation every time. If feasible, the RSForm submission data should be converted to the MySQL-friendly (instead of Human-friendly) date format YYYY-MM-DD and all pre-existing date values should be converted to YYYY-MM-DD as well. This will be painful in the short term, but the long term benefits will be leaner/cleaner queries that are easier to read and maintain AND improved performance on every query execution. This should be a priority job because your database is only going to grow larger; spare yourself many headaches in the future and tidy up your date values.
