separate the column value

Database

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.

4 REPLIES
Enthusiast

Re: separate the column value

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

GROUP BY 1,2

Re: separate the column value

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

Re: separate the column value

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

|hotmail.com | 1

a | gmail.com|  1

b | yahoo.com | 1

|gmail.com  |1

c | hotmail.com |1

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

Enthusiast

Re: separate the column value

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