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.

CodePudding user response：

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;

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;

View working demo on DB Fiddle

CodePudding user response：

you can use :

select package_id , group_concat(concat(currency,' ',grandtotal))
from (
    select package_id,currency, sum(total) grandtotal
    from products 
    group by package_id,currency
) t group by package_id

db<>fiddle here

CodePudding user response：

Try use alias for tables names to identify...

SELECT 
pkg.id,
(
    SELECT
        GROUP_CONCAT(CONCAT(currency,' ',total))
    FROM
        (
            SELECT 
                products.currency AS currency,
                SUM(products.total) AS total
            FROM products
            WHERE products.package_id =  pkg.id
            GROUP BY products.currency
        ) T
) AS total_amount
FROM packages as pkg
LEFT JOIN products
ON products.package_id = pkg.id
GROUP BY pkg.id;