Using GROUP_CONCAT in separate row records

Question

I'm having trouble using GROUP_CONCAT. I'm pretty sure this is the only way to get what I want but it doesn't seem give me the results I need.

Here is my statement:

SELECT
   b.*,
   GROUP_CONCAT(c.finance_code) AS finance_codes
FROM
   `oc_finance_breakpoints` b
   LEFT JOIN
      `oc_finance_breakpoints_codes` c ON c.breakpoint_id = b.breakpoint_id;

This will gather data in the finance_breakpoints table, structure below:

breakpoint_id
from_value
to_value
minimum_deposit

As well as multiple "finance codes" from my join table, finance_breakpoint_codes:

breakpoint_code_id
breakpoint_id
finance_code

There can be, are are likely to be, several finance codes to a breakpoint. When I run the sql when there is only one entry, I get the following:

1 | 280.00 | 750.00 | 10 | ONIF6,ONIF10,ONIF12

But if there are two entries in the breakpoints table, all that happens is it tacks the additional finance codes onto the end of the above, meaning I only ever get one row with the first set of data, and all the finance codes in one column.

Ideally I'd like it to return something such as this:

1 | 280.00 | 750.00 | 10 | ONIF6,ONIF10,ONIF12

2 | 750.00 | 1500.00 | 10 | ONIB12-9.9,ONIB24-9.9,ONIB36-9

Rather than:

1 | 280.00 | 750.00 | 10 | ONIF6,ONIF10,ONIF12,ONIB12-9.9,ONIB24-9.9,ONIB36-9

Is there any way of achieving what I want? Am I maybe using the wrong function?

user359040 · Accepted Answer · 2016-04-18 15:53:24Z

1

The use of an aggregate function (such as GROUP_CONCAT) in your query ensures that it will return aggregated results, while the absence of an explicit grouping ensures that it will return a single, overall summary row.

You need to add a group by clause to the end of your query - like so:

SELECT
   b.*,
   GROUP_CONCAT(c.finance_code) AS finance_codes
FROM
   `oc_finance_breakpoints` b
   LEFT JOIN `oc_finance_breakpoints_codes` c 
          ON c.breakpoint_id = b.breakpoint_id
GROUP BY b.breakpoint_id

answered Apr 18, 2016 at 15:53

user359040

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using GROUP_CONCAT in separate row records

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related