PlotALOT RSForm Query showing only one result

Question

After being helped very well by Stack community and learning a bit I created a query to show the sum of payments per client. A client can pay multiple times. I would like to know the sum of payments in a period per client. So far I managed to see different shelters, the sum of payments but only one client shows up per shelter instead of multiple clients.

This is the query. It's probably an easy thing for someone else, but I don't know where to start or how to search.

The db fiddle link is: https://www.db-fiddle.com/f/84VP3HEQLpiQgYEFEVn919/0

SELECT
    Shelter,
    Date,
    ClientID,
    Sum(Amount) as Amount
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Amount', FieldValue, NULL)) AS Amount,
      Max(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'Date', FieldValue, NULL)) AS Month
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 23
  GROUP BY SubmissionId
) DerivedTable
GROUP BY Shelter

Required output from left to right in table:

Shelter | ClientID | Month | Amount

Bellville | A82QS3OK | 07-2020 | 200.1
Bellville | G993HJOS | 06-2020 | 500
**Bellville | G993HJOS | 08-2020 | 212.6**
Napier Street | BL9B2GDF | 07-2020 | 300

Progress update 2020-07-06: The answer from MickMackUSA works as an example script to answer the question about having the result: Sum per client per month and uses the field "Date" to extract information. https://www.db-fiddle.com/f/84VP3HEQLpiQgYEFEVn919/18

SELECT
    Shelter,
    YEAR(Date) AS Year,
    Month(Date) AS Month,
    ClientID,
    FORMAT(SUM(Amount), 2) AS Amount
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Amount', FieldValue, NULL)) AS Amount,
      Max(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'Date', FieldValue, NULL)) AS Date
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 23
  GROUP BY SubmissionId
) DerivedTable
GROUP BY Shelter, ClientId, YEAR(Date), Month(Date)
ORDER BY Shelter, ClientId, YEAR(Date), Month(Date)

However, I need to know the result per "Monthpayment". That has a date value (YYYY-MM-DD). The field "date" is to define the moment of payment and the field "Monthpayment" is to define for which month the payment is for. That's why I need an addition to the script. I do get the query error message: Error: ER_BAD_FIELD_ERROR: Unknown column "Monthpayment" in 'field list'.

https://www.db-fiddle.com/f/84VP3HEQLpiQgYEFEVn919/19

SELECT
    Shelter,
    Month(Monthpayment) AS Monthpayment,
    ClientID,
    FORMAT(SUM(Amount), 2) AS Amount
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Amount', FieldValue, NULL)) AS Amount,
      Max(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'Monthpayment', FieldValue, NULL)) AS Month
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 23
  GROUP BY SubmissionId
) DerivedTable
GROUP BY Shelter, ClientId, Monthpayment
ORDER BY Shelter, ClientId, Monthpayment

What do I do wrong?

Progress update 2020-07-07 db-fiddle.com/f/84VP3HEQLpiQgYEFEVn919/20

The issue is almost solved:

MAX(IF(FieldName = 'Monthpayment', FieldValue, NULL)) AS Month

Should be

MAX(IF(FieldName = 'Monthpayment', FieldValue, NULL)) AS Monthpayment

However, the database sees two dates with July as different months. Dates 2020-07-06 and 2020-07-09 are seen as two different months.

Answer 1

From this sample data:

INSERT INTO `o2pe0_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
(2124732, 23, 47529, 'SHELTER', 'Bellville'),
(2124728, 23, 47529, 'Amount', '500'),
(2124731, 23, 47529, 'ClientID', 'G993HJOS'),
(2124729, 23, 47529, 'Monthpayment', '2020-07-09'),

(2124743, 23, 47530, 'SHELTER', 'Bellville'),
(2124739, 23, 47530, 'Amount', '255'),
(2124742, 23, 47530, 'ClientID', 'G993HJOS'),
(2124740, 23, 47530, 'Monthpayment', '2020-07-06'),

(2124754, 23, 47531, 'SHELTER', 'Bellville'),
(2124750, 23, 47531, 'Amount', '300'),
(2124753, 23, 47531, 'ClientID', 'G993HJOS'),
(2124751, 23, 47531, 'Monthpayment', '2020-08-06'),

(2124765, 23, 47532, 'SHELTER', 'Bellville'),
(2124761, 23, 47532, 'Amount', '1000'),
(2124764, 23, 47532, 'ClientID', 'A82QS3OK'),
(2124762, 23, 47532, 'Monthpayment', '2020-05-12');

It is important that you are isolating the year and the month values from the date string so that you can reliably group by year AND month.

DB-Fiddle Demo

SELECT
    Shelter,
    ClientID,
    Year,
    Month,
    FORMAT(SUM(Amount), 2) AS Amount
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Amount', FieldValue, NULL)) AS Amount,
      Max(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'Monthpayment', YEAR(FieldValue), NULL)) AS Year,
      MAX(IF(FieldName = 'Monthpayment', MONTH(FieldValue), NULL)) AS Month  
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 23
  GROUP BY SubmissionId
) DerivedTable
GROUP BY Shelter, ClientId, Year, Month
ORDER BY Shelter, ClientId, Year, Month

For anyone who is new to this technique, the DerivedTable is isolating the all submission data for a particular RSForm form, then flattening the many, many rows into flattened rows of data based on the shared SubmissionIds. This sets up the remaining parts of the query to be able to re-group based on Shelter, ClientId, Year, and Month. All of these grouping conditions are done to afford the use of SUM() to return the amount of money paid by respective clients at respective shelters for any given year / month.

The result set for this query is:

| Shelter   | ClientID | Year | Month | Amount   |
| --------- | -------- | ---- | ----- | -------- |
| Bellville | A82QS3OK | 2020 | 5     | 1,000.00 |
| Bellville | G993HJOS | 2020 | 7     | 755.00   |
| Bellville | G993HJOS | 2020 | 8     | 300.00   |