Re: Group by on %like%

On Fri, Jul 3, 2009 at 8:32 PM, Guy Flaherty <naoshika(at)gmail(dot)com> wrote:

>
>
> 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
>

Blah, having said that, you are probably looking for something more like
this:

SELECT "name", substring(phone_number from length(phone_number)-7) AS pn
FROM relation
GROUP BY name,2

GF