2

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.

  • There seems to be a typo or misunderstanding in the 47511 rows. The Date is 08-07-2020, but the Month is 5-2020. It looks like you have too much redundant and needless data in this table. These excess rows are going to impede the efficiency of your queries as your data volume swells. You should store Date as YYYY-MM-DD. You should ditch FormId and Submit. I don't know that you need Month and Month-selection (maybe you have a good reason). Please repair or clarify this mcve: db-fiddle.com/f/84VP3HEQLpiQgYEFEVn919/4 @Gart – mickmackusa Jul 6 at 11:38
  • Or do you want to group on Date instead of Month? db-fiddle.com/f/84VP3HEQLpiQgYEFEVn919/5 – mickmackusa Jul 6 at 12:03
  • Hi @mickmackusa, thanks a lot! I changed the dateformat and the query. Also instead of Month I changed that to Monthpayment. This to clarify the distinction between the payment date and for what month the payment is. So I can pay on 2020-07-06 but it's for 2020-08. We need to know the last one but it doesn't work. The FormID and Submit is standard done by RSFORM so not sure if I can ditch that. I apologize for messing it up. We are in such a busy time and this is also important but also in development. Will try to work next time on a better question and work on the format. – Gart Jul 6 at 13:43
2

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   |
| improve this answer | |
  • Hi @mickmackusa, thank you very very much! This really works very well. In plotalot I see comma's in the year output i.e. 2,020. Not sure why but also not extremely important. For the record, are you the only one replying to posts/questions like these? – Gart Jul 10 at 12:55
  • I don't know if 1. Others can answer but they assume I will resolve it or 2. Others who are capable of constructing these queries visit less frequently and only find the questions after I have answered or 3. No one else in this community can or wishes to answer. No matter the reason, I will go on giving all that I can for the betterment of this community. – mickmackusa Jul 10 at 13:25
  • 1
    Thanks! Well the betterment of this community and beyond. The Haven and their clients are very happy with you. It makes it easier to focus on client care and less on admin. – Gart Jul 10 at 13:53
  • My guess is that you will need to cast 2020 as a string instead of an integer. – mickmackusa Jul 10 at 13:56
  • Thanks! Where do I do that in the query? Tried at various places and googling but can't get it solved. – Gart Jul 11 at 15:42

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.