SQL to count how many ID's are in a table more than once

[lilolee]

Data is like below
RefID - Date
A1 - 01/01/2016
A2 - 01/01/2016
A1 - 02/01/2016
A3 - 02/01/2016
A1 - 03/01/2016
etc

The output I would like is 1, as in only 1 RefID (A1) which occurs more than once.

And then to complicate this, there are many tables ie Fault_Report1, Fault_Report2 etc like this and I would like to know how to know how many are greater than 1 in Total for all Tables.

Regards Lee

 

[lilolee]

This gives me the Ref and Count.

SELECT Reference, count(Reference)
FROM s_Street_lighting___reports
GROUP BY Reference

Is there a way to only show those greater than 1. That would help.

 

[micang]

This gives me the Ref and Count.

SELECT Reference, count(Reference)
FROM s_Street_lighting___reports
GROUP BY Reference

Is there a way to only show those greater than 1. That would help.

SELECT Reference, count(Reference)
FROM s_Street_lighting___reports
GROUP BY Reference
HAVING count(Reference) > 1

 

[Olaf Doschke]

...and counting how many have a count(reference)>1:

Select count(*) From 
(SELECT Reference
FROM s_Street_lighting___reports
GROUP BY Reference
HAVING count(Reference) > 1 )

Bye, Olaf.

 

[lilolee]

Thanks, looks like I need to brush up on my basic sql skills. I'm a Crystal Reports writer really.

 

[Olaf Doschke]

And in regard of Fault_Report1, Fault_Report2 etc you'd union the results of all these queries (via UNION ALL) and sum the count(*). Or you first union all the tables and then do the counting. The results can differ, becaus a reference may be a single value in any of the reports tables but occurring multiple times in the unnioned data.

In any case the UNION has to be a UNION ALL, as a simple UNION removes double values and those are the ones you're interested in.

Bye, Olaf.