substring_index function does not exist error

Question

I have a table named 'data' which stores the email addresses and city of users and I want to find the most popular email domain. I use the following query to to find the rows which have maximum value. exapmple for my table:

Email           Name        City
[email protected]     John    California
[email protected]       Leo sydney
[email protected]      Ross    NY
[email protected]      Ronny   Canberra
[email protected]       Monty   London
[email protected]      Jim washington
[email protected]      Finn    Las vegas

I have computed the answer using this query

  select  x.city, x.No_of_People from (select e.city, count(e.city) as No_of_People from data e group by  e.city) x
where  x.No_of_People = (select max(x2.No_of_People) from (select e2.city, count(e2.city) as No_of_People from data e2 group by         e2.city) x2)

But i don't wan't to use limits as it does not return multiple rows. So I have used the following query using this answer

    select
  x.substring_index (email,'@',-1),
  x.No_of_Users
from
  (select
    e.substring_index (email,'@',-1), 
    count(e.substring_index (email,'@',-1)) as No_of_Users
  from
    data e
  group by 
    e.substring_index (email,'@',-1)) x
where
  x.No_of_Users = 
    (select
      max(x2.No_of_Users)
    from
      (select
         e2.substring_index (email,'@',-1),
         count(e2.substring_index (email,'@',-1)) as No_of_Users
      from
         data e2
      group by 
        e2.substring_index (email,'@',-1)) x2)

The query that i'm using is giving this error "FUNCTION e2.substring_index does not exist". help me.

Gordon Linoff · Accepted Answer · 2012-09-24 14:01:10Z

3

Your syntax is wrong for using the function. The alias goes on the column, not the function. For instance, your last subquery should use this syntax:

  from (select substring_index(e2.email,'@',-1) as strind,
               count(substring_index(e2.email,'@',-1)) as No_of_Users
        from data e2
        group by substring_index (e2.email,'@',-1)
       ) x2

I also named the first column, so you can refer to it outside the subquery if you want.

To count the number of occurrences in a string, use this trick:

(length(e2.email) - length(replace(e2.email, '@', '')) as numAmpersands

edited Sep 24, 2012 at 14:01

answered Sep 24, 2012 at 13:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alonso Over a year ago

yeah man. you'are correct. My syntax is totally illogical. Thanks, You saved my time.

Alonso Over a year ago

Hey..Please tell one more thing. I have corrected the syntax and it is giving correct answer. I mean it is displaying the correct count of number of email domains. But the second line of code is not working. select substring_index(x.email,'@',-1), x.No_of_Users from............... The error is "Unknown column 'x.email' in 'field list" if i remove this line,'substring_index(x.email,'@',-1)', then it works. But i need to show the domain name also.

Alonso Over a year ago

Is there any error in the above statement 'substring_index(x.email,'@',-1)'. how can i display the name??????

Gordon Linoff Over a year ago

@user1690831 . . . You should ask another question on the best way to get the information that you want. This question was quite specific on the substring_index function.

Collectives™ on Stack Overflow

substring_index function does not exist error

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related