Only counting values that appear more than once

Hi guys
I have a table containing serial numbers, some of which occur more than
once. What I want to do is to only count serial number that only occur
more than once. How would I go about doing this?
Many thanksandystob wrote:
> Hi guys
> I have a table containing serial numbers, some of which occur more than
> once. What I want to do is to only count serial number that only occur
> more than once. How would I go about doing this?
> Many thanks
SELECT
SUM(cnt) AS row_cnt,
COUNT(*) AS serial_number_cnt
FROM
(SELECT COUNT(*) AS cnt
FROM your_table
GROUP BY serial_number
HAVING COUNT(*)>1) T ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||If I understand your problem correctly,
select sum(Num) from
(select count(*)
from sometable
group by SerialNumber
having count(*)>1) X(Num)|||This will give you your list of duplicate serial numbers...
select serialnumber
from yourtable
group by serialnumber
having count(*) > 1
You can then use that as a join to do your other query...
select ..
from (
select serialnumber
from yourtable
group by serialnumber
having count(*) > 1 ) as dups
inner join yourtable yt on yt.yourid = dups.yourid
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"andystob" <sql@.evilscience.co.uk> wrote in message
news:1138793435.757970.264220@.o13g2000cwo.googlegroups.com...
> Hi guys
> I have a table containing serial numbers, some of which occur more than
> once. What I want to do is to only count serial number that only occur
> more than once. How would I go about doing this?
> Many thanks
>|||I've tried that code, and I'm getting the following error
Server: Msg 170, Level 15, State 1, Line 21
Line 21: Incorrect syntax near ')'.|||Ah - forgot to add an alias after the last bracket. All working now.
Thanks very much for your help, chaps.|||Hi
SELECT DATEADD(MONTH,DATEDIFF(MONTH,'19000101',
GETDATE()),'19000101')-1
"andystob" <sql@.evilscience.co.uk> wrote in message
news:1138793435.757970.264220@.o13g2000cwo.googlegroups.com...
> Hi guys
> I have a table containing serial numbers, some of which occur more than
> once. What I want to do is to only count serial number that only occur
> more than once. How would I go about doing this?
> Many thanks
>|||Lol Uri - right answer - wrong thread, you want the one before this one ;)
All the best,
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238oncbyJGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Hi
> SELECT DATEADD(MONTH,DATEDIFF(MONTH,'19000101',
GETDATE()),'19000101')-1
>
>
> "andystob" <sql@.evilscience.co.uk> wrote in message
> news:1138793435.757970.264220@.o13g2000cwo.googlegroups.com...
>|||Yep, you are right, I was still thinking about a table variables get
recompiled in our another thread
I 'm going to take a coffee , it is time to relax :-))))
Have a nice day Tony
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eUimJkyJGHA.4068@.TK2MSFTNGP10.phx.gbl...
> Lol Uri - right answer - wrong thread, you want the one before this one ;)
> All the best,
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%238oncbyJGHA.2012@.TK2MSFTNGP14.phx.gbl...
>