1

I am working for a homeless organisation in Capetown. We are using RSForms to register our clients (about 3500 per year). We have a sizable database, but it's very hard to create a dashboard.

We are trying to use vchart or plotalot, but we don't have enough MySQL knowledge and we can't get any further than an example found in JSE. This is already high-end for us: How to display zero values in the legend of PlotALot graph based on RSForm Pro results?

We used this, but we don't actually understand how the different parts of the query work. If we can have another working example, we might be able to work other things out ourselves.

SELECT FieldValue, COUNT(1)
FROM jprefix_rsform_submission_values
WHERE FormId = 14
  AND FieldName = 'SHELTER'
GROUP BY 1
ORDER BY 2 DESC

It would be great if I could extract a table (with rows and columns) or pie chart in PlotALot or any other 3rd party that creates visuals based on MYSQL. For example: Counting people per SHELTER (based on CLIENTID) depending if they are current (so, no exit date).

It would be even greater if the GENDER counts could be split for each SHELTER. So you would see results: SHELTER divided by Male/Female and count based on current CLIENTID, so there is no exit date but there is an entrydate.

You can find a database example here:
https://www.db-fiddle.com/f/p84yz8fmHQAQtuF8uUReFY/0#

1

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.

| improve this answer | |
  • Hi MickMackUsa, we really appreciate your help. This is going to work very well and it's a very good start. Now we can experiment. If I have a question again I publish it here. Thank you so much! The Haven – Gart May 1 at 7:18
  • Just tried to find that solution indeed. I am helped very far now. Will add questions again because trying things out doesnt work yet. For example add the AND function in WHERE to select only one specific Shelter. And a date/month distinction. So I can show results based on 1month/3months. – Gart May 1 at 11:29
  • You are not going to want to filter for Shelters in the derived table (pivot) using WHERE. The WHERE clause in the pivot is very "greedy", only filter by the FormId in that WHERE. You will want to write more conditions into the derived table's HAVING clause. – mickmackusa May 1 at 20:51
  • I have made one final edit to my answer. The final query should be close to what you are looking for. I must refuse to extend this post further because this page is getting too long and "shifting goalposts" questions are not suitable here (and in most cases can irritate volunteers). I'll wish you good luck and if this answer has sufficiently resolved your question, please award the green tick so that this page is deemed resolved within the system. @Gart – mickmackusa May 1 at 22:30
  • Just great! With all the terms, scripts and examples I should be able to work with this for our homeless organisation. – Gart May 2 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.