How to count and Duplicate in sql server

Posted by Jayakumars under Sql Server on 8/2/2018 | Points: 10 | Views : 133 | Status : [Member] [MVP] | Replies : 1
Hi
How to Count Query for this in sql server
ex: Only Email id based Duplicate count only.

--Emailid Date
--abc@gmail.com 11-04-2018
--abce@gmail.com 12-04-2018
--abc@gmail.com 13-04-2018
--abcr@gmail.com 14-04-2018
--abc@gmail.com 15-04-2018
--abce@gmail.com 18-04-2018
--abct@gmail.com 11-04-2018
--abcf@gmail.com 12-04-2018

I need 2 separate output here 2 separate Query for this

-- First output

-- Only EmailId based Duplicate Output
-- Total Emails (5)

--- Notes here Duplicate abc@gmail.com(3),abce@gmail.com(2) so output Total 5

--Second Output
-- abc@gmail.com(3)
-- abce@gmail.com(2)

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com



Responses

Posted by: Sriharim on: 8/5/2018 [Member] Starter | Points: 25

Up
0
Down
Output 1 Query:
Single Column output with text 'Total Emails'
select 'Total Emails ('+cast(sum(DuplicatsID) as varchar) + ')'as [Total Emails] from 

(select emailid,count(*) as DuplicatsID from EmailTesting group by emailid having count(*)>1) f
or
select sum(DuplicatsID) as [Total Emails] from 

(select emailid,count(*) as DuplicatsID from EmailTesting group by emailid having count(*)>1) f

Output 2 Query:
Column output
select emailid+' ('+ cast(count(*) as varchar)+')' as DuplicatesID from EmailTesting group by emailid having count(*)>1


Or
select emailid,count(*) as DuplicatesID from EmailTesting group by emailid having count(*)>1



 Download source file

Mark as Answer if its helpful to you
---
Srihari

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response