How to count and Duplicate in sql server

Posted by Jayakumars under Sql Server on 8/2/2018 | Points: 10 | Views : 1598 | Status : [Member] [MVP] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

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

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

Latest Posts