MySQL ask to add column to GROUP BY which I don't want to use in GROUP BY

Question

I have a query that should create a view that will be used to be exported as csv file. Here's the query:

CREATE OR REPLACE VIEW coupons_export AS
    SELECT
        coupons.code AS coupon,
        CONCAT('example.com/', programs.identifier, '/', coupons.code) AS url,
        GROUP_CONCAT(items.name) AS items,
        channels.name AS channel_name,
        programs.name AS program_name,
        SUM(coupon_stocks.current_quantity) AS current_quantity,
        SUM(coupon_stocks.sent_quantity) AS sent_quantity
    FROM
        coupons
    INNER JOIN
        channels ON coupons.channel_id = channels.id
    INNER JOIN
        coupon_stocks ON coupons.id = coupon_stocks.coupon_id
    INNER JOIN
        items ON coupon_stocks.item_id = items.id
    INNER JOIN
        programs ON coupon_stocks.program_id = programs.id
    WHERE
        coupons.channel_id = 1
    group by
        coupon

The problem with this query is that MySQL throw an error saying that I need to add the programs.identifier to the GROUP BY, but I don't want to aggregate the view by that field. What I want to get as result is one row per 'coupon' and the related 'items' concatenated in one column as the following:

coupon	url	items	channel_name	program_name	current_quantity	sent_quantity
CSV2WGQH	url.com/delivery/CSV2WGQH	item 1, item 2	instagram	delivery	3	0
CSVIFOQK	url.com/delivery/CSVIFOQK	item 1	instagram	delivery	1	1
CSV5KSDF	url.com/delivery/CSV5KSDF	item 1, item 2	instagram	delivery	3	3
CSVOSVAH	url.com/delivery/CSVOSVAH	item 1, item 2	instagram	delivery	3	3

but adding the programs.identifier to the GROUP BY what I get is only two rows with all the items as the following:

coupon	url	items	channel_name	program_name	current_quantity	sent_quantity
CSV2WGQH	url.com/delivery/CSV2WGQH	item 1, item 2, item 1, item 2, ...	instagram	delivery	5050	0
CSVIFOQK	url.com/delivery/CSVIFOQK	item 1, item 1, item 1, item 1, ...	instagram	delivery	5050	12

I could remove the GROUP BY, but this way I'll get an entrie for each item of each coupon, and I just want one entrie for coupon. Anyone can help me with that? I put an example DB at fiddle so you can try.

Thx

Solution

The accepted answer by @tinazmu and the comments by @Schwern, @ErgestBasha and @Eric helped me to undertand what was the problem with my query and how I should write it. The comment by @ErgestBasha have a working example of the query, although it won't work properly on fiddle, the query works fine with my RDBMS and my application. Here's the working query by @ErgestBasha:

CREATE OR REPLACE VIEW coupons_export AS
    SELECT
        coupons.code AS coupon,
        MAX(CONCAT('example.com/', programs.identifier, '/', coupons.code)) AS url, ### Added MAX() 
        GROUP_CONCAT(items.name) AS items,
        channels.name AS channel_name,
        MAX(programs.name) AS program_name, -- Added MAX() 
        SUM(coupon_stocks.current_quantity) AS current_quantity,
        SUM(coupon_stocks.sent_quantity) AS sent_quantity
    FROM
        coupons
    INNER JOIN
        channels ON coupons.channel_id = channels.id
    INNER JOIN
        coupon_stocks ON coupons.id = coupon_stocks.coupon_id
    INNER JOIN
        items ON coupon_stocks.item_id = items.id
    INNER JOIN
        programs ON coupon_stocks.program_id = programs.id
    WHERE
        coupons.channel_id = 1
    group by
        coupon

Thanks everyone for the help

Answer 1

You want to group by coupon: this means that you want one row per coupon.

You also want to see programs.identifier, channels.name, and programs.name. MySQL doesn't know what to do if it encounters more than one value of programs.identifier for a given coupon (excepting cases where MySQL infers functional dependencies, see the link by @Schwern).

If you know that these never change (ie. program.identifier is always the same for each coupon) then adding it to the GROUP BY is the right action. This requests one row per each combination of coupon and programs.identifier. On the other hand, if you know that the program.identifier can change for the same coupon and you still want to see one value of programs.identifier for each coupon, and you don't care which program.identifier is selected, simply add an aggregation function, like MIN/MAX around program.identifier.

Same consideration applies to channels.name, programs.name.