MySQL GROUP_CONCAT with SUM() and multiple JOINs inside subquery

Question

I'm very average with MySQL, but usually I can write all the needed queries after reading documentation and searching for examples. Now, I'm in the situation where I spent 3 days re-searching and re-writing queries, but I can't get it to work the exact way I need. Here's the deal:

1st table (mpt_companies) contains companies:

| company_id | company_title |
------------------------------
| 1          | Company A     |
| 2          | Company B     |

2nd table (mpt_payment_methods) contains payment methods:

| payment_method_id | payment_method_title |
--------------------------------------------
| 1                 | Cash                 |
| 2                 | PayPal               |
| 3                 | Wire                 |

3rd table (mpt_payments) contains payments for each company:

| payment_id | company_id | payment_method_id | payment_amount |
----------------------------------------------------------------
| 1          | 1          | 1                 | 10.00          |
| 2          | 2          | 3                 | 15.00          |
| 3          | 1          | 1                 | 20.00          |
| 4          | 1          | 2                 | 10.00          |

I need to list each company along with many stats. One of stats is the sum of payments in each payment method. In other words, the result should be:

| company_id | company_title | payment_data            |
--------------------------------------------------------
| 1          | Company A     | Cash:30.00,PayPal:10.00 |
| 2          | Company B     | Wire:15.00              |

Obviously, I need to:

Select all the companies;
Join payments for each company;
Join payment methods for each payment;
Calculate sum of payments in each method;
GROUP_CONCAT payment methods and sums;

Unfortunately, SUM() doesn't work with GROUP_CONCAT. Some solutions I found on this site suggest using CONCAT, but that doesn't produce the list I need. Other solutions suggest using CAST(), but maybe I do something wrong because it doesn't work too. This is the closest query I wrote, which returns each company, and unique list of payment methods used by each company, but doesn't return the sum of payments:

SELECT *,
(some other sub-queries I need...),
(SELECT GROUP_CONCAT(DISTINCT(mpt_payment_methods.payment_method_title))
FROM mpt_payments
JOIN mpt_payment_methods
ON mpt_payments.payment_method_id=mpt_payment_methods.payment_method_id
WHERE mpt_payments.company_id=mpt_companies.company_id
ORDER BY mpt_payment_methods.payment_method_title) AS payment_data
FROM mpt_companies

Then I tried:

SELECT *,
(some other sub-queries I need...),
(SELECT GROUP_CONCAT(DISTINCT(mpt_payment_methods.payment_method_title), ':',  CAST(SUM(mpt_payments.payment_amount) AS CHAR))
FROM mpt_payments
JOIN mpt_payment_methods
ON mpt_payments.payment_method_id=mpt_payment_methods.payment_method_id
WHERE mpt_payments.company_id=mpt_companies.company_id
ORDER BY mpt_payment_methods.payment_method_title) AS payment_data
FROM mpt_companies

...and many other variations, but all of them either returned query errors, either didn't return/format data I need.

The closest answer I could find was MySQL one to many relationship: GROUP_CONCAT or JOIN or both? but after spending 2 hours re-writing the provided query to work with my data, I couldn't do it.

Could anyone give me a suggestion, please?

Consider handling issues of data display in the presentation layer/application-level code, assuming you have that (e.g. a simple PHP loop acting upon an ordered array). — Strawberry
Yes, I will use PHP to process "payment_data" field to format it the way I need, but I strongly believe that calculating sums and grouping payments with MySQL query is a much more effective solution instead of using PHP loops. — toxpal

sticky bit sticky bit · Accepted Answer · 2019-12-09T10:22:06

You can do that by aggregating twice. First for the sum of payments per method and company and then to concatenate the sums for each company.

SELECT x.company_id,
       x.company_title,
       group_concat(payment_amount_and_method) payment_data
       FROM (SELECT c.company_id,
                    c.company_title,
                    concat(pm.payment_method_title, ':', sum(p.payment_amount)) payment_amount_and_method
                    FROM mpt_companies c
                         INNER JOIN mpt_payments p
                                    ON p.company_id = c.company_id
                         INNER JOIN mpt_payment_methods pm
                                    ON pm.payment_method_id = p.payment_method_id
                    GROUP BY c.company_id,
                             c.company_title,
                             pm.payment_method_id,
                             pm.payment_method_title) x
      GROUP BY x.company_id,
               x.company_title;

db<>fiddle

MySQL GROUP_CONCAT with SUM() and multiple JOINs inside subquery

2 Answers