CODEWITHSUNDEEP
mysqlconcatenationgroup-concat
Martin
Martin

Reputation: 10563

GROUP_CONCAT around CONCAT gives an error - Invalid use of group function

I'm trying to GROUP_CONCAT the results returned by the following query:

SELECT
      CONCAT(p.product_name, " (", SUM(bp.quantity), ")") AS products
FROM product p
INNER JOIN booking_products bp ON p.product_id = bp.product_id
GROUP BY p.product_id

This query returns the following:

------------
| products |
------------
Shampoo (3)
Advil (1)
Dry Shampoo (4)
Cepacol (5)

When I try run the query below I get an error: "#1111 - Invalid use of group function". What is wrong with this?

SELECT
    GROUP_CONCAT(
        CONCAT(p.product_name, " (", SUM(bp.quantity), ")") 
        SEPARATOR ", "
    ) AS products
FROM product p
INNER JOIN booking_products bp ON p.product_id = bp.product_i
GROUP BY p.product_id

Surely it should return:

Shampoo (3), Advil (1), Dry Shampoo (4), Cepacol (5)

Thanks for any help.

Upvotes: 0

Views: 5510

Answers (1)

Achrome
Achrome

Reputation: 7821

SELECT GROUP_CONCAT(products) FROM
(
    SELECT CONCAT(p.product_name, " (", SUM(bp.quantity), ")") AS products
    FROM product p
    INNER JOIN booking_products bp ON p.product_id = bp.product_id
    GROUP BY p.product_id
) x

GROUP_CONCAT is not working because you already used SUM, which is a GROUP function.

Upvotes: 7

Related Questions