1

I have a query that finds housenumbers per street in a specific postcode with four numbers:

SELECT *,
    GROUP_CONCAT(DISTINCT CAST(housenumber AS int) ORDER BY housenumber ASC) AS housenumbers
FROM streets
WHERE postcode LIKE '3031%'
GROUP BY street
ORDER BY lat,lon

This is the result of the groupcatted housenumbers.

491,492,493,500,501,502,503,504,505,506,507,508,50

I would like to split the result, over two columns, in evenhousenumber and oddhousenumbers. I tried MOD and %2, but both require a WHERE and it's not possible to use the WHERE in the groupcat function, I noticed.

How can I split the result, over even and odd numbers?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Jeroen Steen
  • 531
  • 8
  • 22
  • Why would you be converting the housenumbers to integers to concatenate them into a string? – Gordon Linoff Dec 26 '19 at 16:59
  • Because I want to have a short list, that's printable. For delivery of flyers door-to-door, it's not essential to have all the letters etc. on the list. – Jeroen Steen Dec 26 '19 at 17:12
  • something called house**number** has values other than numbers? – Gordon Linoff Dec 26 '19 at 17:23
  • @GordonLinoff, It's not uncommon for a second home unit to exist at the same address, and they are lettered. Like "221B Baker Street", the address of detective Sherlock Holmes. – Bill Karwin Dec 26 '19 at 18:13

1 Answers1

5

You could use CASE expression:

SELECT street
 ,GROUP_CONCAT(DISTINCT CASE WHEN MOD(CAST(housenumber AS int),2) = 0 THEN CAST(housenumber AS int) END ORDER BY housenumber ASC) AS housenumbers_even
 ,GROUP_CONCAT(DISTINCT CASE WHEN MOD(CAST(housenumber AS int),2) = 1 THEN CAST(housenumber AS int) END ORDER BY housenumber ASC) AS housenumbers_odd
FROM streets
WHERE postcode LIKE '3031%'
GROUP BY street
ORDER BY lat,lon;

It works because GROUP_CONCAT skips NULL values.

Also SELECT * ... GROUP BY street is not safe. Related: Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275