[SOLVED] Count double rows

[Miko]

Hello,

 

i'm building a query that counts how many records I have in my database, this by using a code.

 

My query:

 

SELECT * FROM DB WHERE scan = '5' AND nr BETWEEN 'x' AND 'y'

 

Now the scan is not a variable so it is always 5, but the x and y are variables.

 

So I can get these rows for exemple:

 

5000

5002

5002

5002

5003

5006

5008

5008

5010

 

etc etc ..

 

Now I would like to count those that are double.

I know that I should do this with a count:

 

SELECT COUNT(field)AS test FROM DB .......

 

But I'm a bit stuck here

Anyone knows how I can do that?

 

Thanks

[ignace]

I'm not entirely sure about this there may be more performant solutions then this one:

SELECT * FROM table WHERE field_that_has_doubles IN (SELECT field_that_has_doubles FROM table)

 

Plus it possible is going to get moved to the Mysql Help

[rhodesa]

SELECT nr,COUNT(*) FROM DB WHERE scan = '5' AND nr BETWEEN 'x' AND 'y' GROUP BY nr

[Miko]

hi thanks!

 

But how do I echo the amount of doubles?

 

Thanks!

[rhodesa]

um...can you clarrify what exactly you want outputted? If your table was like this:

5000
5002
5002
5002
5003
5006
5008
5008
5010

 

what number do you want? the select query i gave you will return each unique nr and how many there are of each. do you want 2 insetad (which is the number of nr's that have at least doubles)? or 5 (which is the number of rows that have duplicates)?

[Miko]

ah found it,

 

SELECT nr,COUNT(nr) AS total FROM DB WHERE scan = '5' AND nr BETWEEN 'x' AND 'y' GROUP BY nr

 

[Miko]

actually it was to know how many records I have with the same value in the field 'nr' using some arguments

 

thanks!