Countin duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Countin duplicates

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2010-06-08 : 17:41:45

Hi, ok long post coming up so here goes:

I have a database for tennis matches, consisting of records of matches,
to check there was no duplicates I have (checking by dates)

SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches 
GROUP BY Date
HAVING (count(Date) > 1)

This gives :_

The Match_ID is the unique primary key ID for a match.
So this is just showing the 1st Match_ID (but all these have
2 entries).

So expanding on this , if I use

SELECT p1.player_name as 'Player1',
p2.player_name as 'Player2',
m.Result as 'Result',
m.Score as 'Score',
m.Date as 'Date',
m.surface as 'Surface'
from matches m , players p1, players p2
WHERE 
p1.ID = m.Player1_ID
and p2.ID = m.Player2_ID
and date in (
SELECT x.Date from
(
SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches 
GROUP BY Date
HAVING (count(Date) > 1) 
) x
)
order by m.Date desc

This query joins the player table too, but gives me the TWO matches
with the 2 dates - below 16 instead of 8. (using image as text wont allign)

So, is it possible to get a similar result but without using nested code like i did in the 2nd code sample ?

more like the 1st one ?

Thanks

pazzy11
Posting Yak Master

145 Posts

Posted - 2010-06-09 : 07:47:41

OK, to simplify my question .
With Duplicates

SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches 
GROUP BY Date
HAVING (count(Date) > 1)

Gives the 8 matches, but there are 16 (2 on each date) ,
is there a simple adjustment i can make to the above code to get the 16 ?

rather then the below (which returns the 16 but it's not good to have so much nested code.)

[CODE]SELECT Match_ID,Date
FROM matches
where Date in
(
SELECT x.Date from
(
SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches
GROUP BY Date
HAVING (count(Date) > 1)
) x
)
ORDER BY Date desc [/CODE]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-06-09 : 08:21:08

how can you run this code:

SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches 
GROUP BY Date
HAVING (count(Date) > 1)

without getting the error

'match_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

pazzy11
Posting Yak Master

145 Posts

Posted - 2010-06-09 : 08:32:29

Good point ! , well I am using MySQL for this particular task, normally i use MS SQL 2008...

pazzy11
Posting Yak Master

145 Posts

Posted - 2010-06-09 : 08:47:27

So now to make it SQL 2008 friendly ;)

[CODE]
SELECT
Date as 'Date',
count(Date) as 'occurences'
FROM matches
GROUP BY Date
HAVING (count(Date) > 1)
[/CODE]

Gives the 8 matches, but there are 16 (2 on each date) ,
is there a simple adjustment i can make to the above code to get the 16 ?

rather then the below (which returns the 16 but it's not good to have so much nested code.)

SELECT Match_ID,Date
FROM matches
where Date in
(
SELECT x.Date from
(
SELECT
Date as 'Date',
count(Date) as 'occurences'
FROM matches
GROUP BY Date
HAVING (count(Date) > 1)
) x
)
ORDER BY Date desc

Sachin.Nand

2937 Posts

Posted - 2010-06-09 : 08:49:57

Try this


SELECT Match_ID,x.Date,occurences from
(
SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches 
GROUP BY Date,Match_ID
HAVING (count(Date) > 1)
) x inner join matches on matches.Match_ID=x.Match_ID
ORDER BY Date desc

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

pazzy11
Posting Yak Master

145 Posts

Posted - 2010-06-10 : 11:15:09

I see where you are going Idera, good idea, but this gets 0 rows. In MS SQL 2008 and Mysql (i have copied tables over to MS SQL 2008)
It gets 0 rows because this:


SELECT Match_ID,
Date as 'Date',
count(Date) as 'occurences'
FROM matches 
GROUP BY Date,Match_ID
HAVING (count(Date) > 1)

Gives 0.
it gives 0 because it is looking for duplicated dates with duplicated Match_ID's
which is impossible cos Match_ID is the PK.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources