Re: Group by on %like%

On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey <jennifer(dot)trey(at)gmail(dot)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

The substring bit is the part you will have to work out in order to make
sure you get the correct rows returning you are looking for. This is just an
example :)

Regards,
GF