Michael Emerson Michael Emerson - 7 months ago 12
SQL Question

Using GROUP_CONCAT in separate row records

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?

Answer

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
Comments