Geek asks and answers

Extracting GROUP BY Results

Let's say I have a table like this:

name        | address
------------+----------------
JOHN SMITH  | 123 FAKE ST
JANE SMITH  | 123 FAKE ST
DAN JOHNSON | 456 WHATEVER RD

Now let's say I create a view where I do GROUP BY address, resulting in something like this:

name                   | address         | group_id
-----------------------+-----------------+---------
JOHN SMITH, JANE SMITH | 123 FAKE ST     | 1
DAN JOHNSON            | 456 WHATEVER RD | 2

Is there a way, using only SQL, to "expand" the results of this grouping, for example?

name        | address         | group_id
------------+-----------------+---------
JOHN SMITH  | 123 FAKE ST     | 1
JANE SMITH  | 123 FAKE ST     | 1
DAN JOHNSON | 456 WHATEVER RD | 2
+3
source share
3 answers

Yes, since ypercube noted that this is possible, and you will need the SUBSTRING_INDEX function. You will also need to regenerate rows that are complex since mysql does not support recursive queries.

You can make a workaround, here is a solution assuming a maximum of 3 entries to just illustrate:

SELECT SUBSTRING_INDEX(name, ',', 1), address, group_id
FROM aggregated a1
WHERE
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 2), ',', -1), address, group_id
FROM aggregated a2
WHERE name LIKE '%,%'
UNION ALL
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', 3), ',', -1), address, group_id
FROM aggregated a3
WHERE name LIKE '%,%,%'

, , ( ): - .

, ( , , , , ).

EDIT: group_concat ( ), . - ( ).

0

Use GROUP BY a.name. Why do you want to group it by address?

You will still use WHERE a.address=b.addresscorrectly!

-1
source

More articles:


All Articles