Finding duplicate values in a SQL

Question

0.00/5 (No votes)

See more:

In below query there is a column called lead_id i want to find count of duplicate lead_id
in my result if there are 10 unique lead id i must get 10
but it must be grouped based on created_time ie.. if there are 2 unique lead_id for today's date then the result would be 2 ..

PS: i want only count of the duplicate data but i need to group by created_time only

i tried like this

SQL

SELECT COUNT(*) as repetitions ,lead_id
 FROM mg_lead_suggested_listing
 GROUP BY lead_id
 HAVING repetitions > 1;

but it will not fit to my requirement as i need to group by time_created

SQL

select
    t.created_time,
    t.timecreated,
    sum(t.suggested_pending_cnt),
    sum(t.suggested_dropped_cnt)
from
    (select
        date_format(timecreated, '%d-%b-%Y') created_time,
            timecreated,
            case
                when source = 2 then 1
                else 0
            end suggested_pending_cnt,
            case
                when (source = 2 && directory_status = 4) then 1
                else 0
            end suggested_dropped_cnt
    from
        mg_lead_suggested_listing) t
group by t.created_time
order by t.timecreated desc
limit 10

Posted 24-Nov-14 22:02pm

Member 10955199

Updated 25-Nov-14 1:06am

King Fisher

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Answer 1 · 2014-11-24T22:12:00

check this

SQL

SELECT COUNT(lead_id) as repetitions ,Convert(Date,created_time)
FROM mg_lead_suggested_listing
GROUP BY Convert(Date,created_time)
HAVING repetitions > 0;

Additionally you can also try

SQL

select * from
(
select  
   Row_number() Over 
             (Partition by Convert(Date,created_time) Order by   
                          Convert(Date,created_time)) as repetitions 
   ,*
from mg_lead_suggested_listing 
) a where repetitions>1

Ashish_Pathania · Answer 2 · 2014-11-25T01:28:00

Solution 2

;with cte_dup
as
(
select ROW_NUMBER() over( partition by columnname1 order by columnname1) as rno, * from Tablename
)
select * from cte_dup where rno>=2

Posted 25-Nov-14 1:28am

Ashish_Pathania