SQL - distinct/counts?

[clonmult]

I haven't had to delve into any SQL for bleedin' years (probably over 10, since I last worked on Foxpro), but got something that I've got to do in it now .... help?

Got an incident logging system thats got an interface from a network management system that sends through updates (they'll be logged by the same user-id), but it appears that something in the system is generating duplicate incidents.

Done a couple of basic SQL queries :

select count(alert_id) from incidents where submitter='blah'
select count(distinct(alert_id)) from incidents where submitter='blah'

Which demonstrates the scale of the problem of the duplication - how many incidents have been raised by the system user, and how many distinct (ie. non-duplicate) incidents.

However I now need to do an extract to identify the incidents that have been duplicated, basically to give the alert ids and the number of duplicates against each.

Something using :

select distinct(alert_id) <count bit???> from incidents where submitter='blah'

So yes, my SQL skills are incredibly weak, and I'm begging for help ....

[anonymous-user]

select alert_id, count(alert_id) from incidents where submitter='blah' group by alert_id having count(alert_id) > 1

Or something like that.

[clonmult]

Many thanks! There was me thinking I was going to have to do sub queries and the like. works perfectly!

[anonymous-user]