SQL - GROUP BY and SUM using joined table fields

Question

Let's assume there are two different tables:

PACKAGE

id
--
1
2
3

PRODUCT

id | package_id | currency | total
----------------------------------
1  | 1          | USD      | 100
2  | 1          | EUR      | 200
3  | 2          | USD      | 300
4  | 2          | USD      | 400
5  | 3          | GBP      | 500

And the desired result is to get concatenated total amounts for each package (from its products) by each DISTINCT currency, something like:

id | total_amount
----------------------
1  | USD 100, EUR 200
2  | USD 700
3  | GBP 500

Tried with this query:

SELECT 
packages.id,
(
    SELECT
        GROUP_CONCAT(CONCAT(currency,' ',total))
    FROM
        (
            SELECT 
                products.currency AS currency,
                SUM(products.total) AS total
            FROM products
            WHERE products.package_id = [[ packages.id ]] -- double brackets just for marking
            GROUP BY products.currency
        ) T
) AS total_amount
FROM packages
LEFT JOIN products
ON products.package_id = packages.id
GROUP BY packages.id;

But there is an error that package.id (in double brackets above) is not visible, probably because of the subquery depth.

Is there any way that it can be achieved? Thanks.

Answer 1

You may try rewriting your query as a left join instead of a subquery which may be more efficient or faster as shown below:

SELECT
    p.id,
    ct.currency_totals
FROM
    packages p
LEFT JOIN (
    SELECT
        package_id,
        GROUP_CONCAT(
            CONCAT(currency,' ',total)
        ) as currency_totals
    FROM (
        SELECT
            package_id,
            currency,
            SUM(total) as total
        FROM
            products
        GROUP BY
            package_id,
            currency
    ) t
    GROUP BY
        package_id
) ct on ct.package_id=p.id;

id	currency_totals
1	USD 100,EUR 200
2	USD 700
3	GBP 500

---

Moreover, if you only require the package id of currently used packages and no other details , using your subquery may be enough for this task.

SELECT
        package_id,
        GROUP_CONCAT(
            CONCAT(currency,' ',total)
        ) as currency_totals
    FROM (
        SELECT
            package_id,
            currency,
            SUM(total) as total
        FROM
            products
        GROUP BY
            package_id,
            currency
    ) t
    GROUP BY
        package_id;

package_id	currency_totals
1	USD 100,EUR 200
2	USD 700
3	GBP 500

---

View working demo on DB Fiddle