Re: Group by on %like% - Mailing list pgsql-general

On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer.trey@gmail.com> wrote:

Hi,

I would like to run a query and group several rows based on a phone number.

However, the same phone number might have a prefix on occasion, example :

name | phone_number
----------------------
james | 123456
james | 00441234556
james | 555666
sarah | 567890
sarah | 567890

as you can see, the first 2 James seems to belong together.

running

select name, phone_number from relation group by name, phone_number

would not reflect this.

I don't think there is a way to run something similar to this :

select name, phone_number from relation group by name, %phone_number%
// or similar

However, I believe there is a way, so I would like to here it from you :)

Functions, sums .. please let me know..

Thank you in advance / Jennifer

You could run a sub-select first to get your results and then group on that, such as:

SELECT name, pn
FROM
(SELECT name, substring(phone_number from length(phone_number)-7) AS pn
FROM relation
WHERE phone_number LIKE '%1234%') AS r
GROUP BY name,pn