All Forums Database
Chinu_Teradata 3 posts Joined 05/15
27 May 2015
separate the column value

Hi,
Here i my table
Eid​| Ename| Did| Email

 

5|E |40| e@gmail.com

 3|C| 30| c@hotmail.com

 1 |A |10 |a@gmail.com

6|F |40 |e@gmail.com

4 |D |40| d@hotmail.com

2| B |20 |b@yahoo.com

 

 

i want to separate name and domain name from Email column and want to count no. of diffrent mail id.

Can anyone help..

thanks in advance.

 

 

yuvaevergreen 93 posts Joined 07/09
28 May 2015

SEL STRTOK(Email,'@',1) AS NAME, STRTOK(Email,'@',2) AS DOMAINNAME, COUNT(DISTINCT(Email)) FROM TABLE
GROUP BY 1,2
 

Chinu_Teradata 3 posts Joined 05/15
28 May 2015

its displaying syntax error: Data type "Email" does not matcha defined type name
 
 

Chinu_Teradata 3 posts Joined 05/15
28 May 2015

sel substr(Email,1,position('@' in Email)-1) as Name,

substr(Email,position('@' in Email)+1) as Domain_name,count(distinct Email) No_mail from Employee

group by 1,2

 

Above query giving output as below:

 

Name|  Domain_name|  No_mail

d  |hotmail.com | 1

a | gmail.com|  1

b | yahoo.com | 1

e  |gmail.com  |1

c | hotmail.com |1

 

But i need gmail as count 2, yahoo count 1 and hotmail count 2

 

 

Tuen 44 posts Joined 07/05
28 May 2015

The count you got is correct based on how your returning the data.  You grouped by Name and Domain_Name,  So in this case,
Name = a
Domain_Name = gmail.com
 
is not the same as
 
Name = e
Domain_Name = gmail.com
 
which is why you got each with a count of 1.  You woul need to leave out the name and run the count against just the domain_name.
using the strtok example.
 

SEL 

  STRTOK(Email,'@',2) AS DOMAIN_NAME, 

  COUNT(domain_name) 

FROM mytable

group by 1;

 

yahoo.com                                                 1

hotmail.com                                               2

gmail.com                                                 3

 

 

using the substr

 

sel 

substr(Email,position('@' in Email)+1) as Domain_name,

count(Domain_Name) No_mail 

from financial.mytable

group by 1;

 

the distinct will only count a single instance of the domain name so you need to remove it.  Now, you can pull this in one query the name and such in the other and join to them on Domain_Name.

you could do the following to get the count with the names, or modify it to get the actual results you want.
 

select

a.name,

a.domain_name,

b.count_of_domain

from (

Select

substr(Email,1,position('@' in Email)-1) as name,

substr(Email,position('@' in Email)+1) as Domain_name

from mytable

group by 1,2

) a

inner join

(

sel 

substr(Email,position('@' in Email)+1) as Domain_name,

count(Domain_Name) as count_of_domain 

from mytable

group by 1

) b

on a.domain_name = b.domain_name

group by 1, 2, 3;

 

f

gmail.com

3

e

gmail.com

3

d

hotmail.com

2

c

hotmail.com

2

b

yahoo.com

1

a

gmail.com

3

 

You must sign in to leave a comment.