Question:
These are the fields of my table: id, email, firstname, and lastname. There can be multiple records for a single email. I only want to group by email and get a count of how many records have the same email for each group. The fields I need for the report are firstname, lastname, email, and emailcount. If two records have the same email but different firstname or lastname, I only need to see one of them in the recordset.
Answer:
You can use a query with the GROUP BY clause to retrieve a count of columns that contain the same information. For example, assume you have a table with the four columns you specified and four records, two of which have the same e-mail address. This query returns the count of records with the specified e-mail address:
SELECT Count(*) AS emailcount, email FROM UsersGROUP BY emailemailcount email ---------- ----------------------- 2 [email protected] [email protected] [email protected]
This query gets a little fancier and groups by the additional columns in the table. Only columns in the GROUP BY clause can be returned in a GROUP BY query.
SELECT Count(*) AS emailcount, email, firstname, lastname FROM UsersGROUP BY email, firstname, lastnameemailcount email firstname lastname ---------- ---------------------- --------- --------2 [email protected] Anne Smith1 [email protected] Jack Jones1 [email protected] Mary Murray
