counting non-unique rows in table with additional criteria

Question

Hi,

I have a table USERS with the following fields

date(datetime) email(varchar) provider(int) event(int)

I am looking for how many records there are with the same email, which occur in a specific month with a specific provider.

like for provider= x and month = y i want

email              occurs
[email protected]    5
[email protected]  2

I know it should be WHERE provider= x and MONTH(date) = y, but how can i get how a count of all the emails out of the table? is it possible?

sorry if this isnt very clear! thanks for your help

Answer 1

This should work:

SELECT email, COUNT(*) AS occurs
WHERE provider = x AND YEARMONTH(date) = y
GROUP BY email
HAVING occurs > 1

Answer 2

Hi,

I have a table with one filed that doesn't have any primary key. Such as DNC->DNC_No It has no primary key and want to update a list of No that holds mulitple duplicates no as well as in master table also. I want to update only unique No.

Could you please tell me what is the query made in SQL for that.

With Regards, Traique Nasir