CODEWITHSUNDEEP
sqlsql-server
Thor
Thor

Reputation: 10028

How to reuse the same statement within a "select" and a "group by" clause?

I'm trying to obtain the domain name of all email address using the string function substring() and charindex(), and then group it and count the number of emails with the same domain name.

My query:

declare @SearchTerm nvarchar(1) = '@'
select substring(Email, charindex(@SearchTerm, Email) + 1, len(Email)) as [Email Domain],
count(Email) as Total 
from tblResident 
group by substring(Email, charindex(@SearchTerm, Email) + 1, len(Email))

But as you can see, the statement used in the "group by" clause is the same as the one I used in "select" statement. It is kind of long and I dont want to write it down twice. So I was just wondering if there is a way I can write substring(Email, charindex(@SearchTerm, Email) + 1, len(Email)) only once but still achieve the same result?

My table:

enter image description here

Result:

enter image description here

Upvotes: 0

Views: 1352

Answers (3)

Teja
Teja

Reputation: 13524

WITH t AS ( SELECT SUBSTRING(email,3,LEN(email)) AS emaildomain 
FROM tblresident )
SELECT emaildomain,COUNT(*)
FROM t
GROUP BY emaildomain;

Upvotes: 1

Esty
Esty

Reputation: 1912

First of all, if you want J.com once for both [email protected] and [email protected]; means you have to do group by with expression then what you have done is the best way.

On the other hand, if you want J.com twice from [email protected] and [email protected] then you could have do as following;

SELECT substring(Email, charindex(@SearchTerm, Email) + 1, len(Email)) as [Email Domain],
COUNT(Email) as Total 
FROM tblResident 
GROUP BY Email

Upvotes: -1

jyao
jyao

Reputation: 1630

-- prepare data
use tempdb
drop table dbo.tblResident;
create table dbo.tblResident (id int identity, name varchar(30), Email varchar(30));
go
insert into dbo.tblResident (name, email)
values ('Justin', '[email protected]'), ('Nancy', '[email protected]'), ('Evee', '[email protected]'), ('Coco', '[email protected]'), ('Jess', '[email protected]');    
go 
-- here is the query
   ;with c as (
    select substring(Email, charindex(@SearchTerm, Email) + 1, len(Email)) as [Email Domain]
    from tblResident )
    select [Email Domain],  Total=count(*)
    from c 
    group by [Email Domain];

Upvotes: 3

Related Questions