Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 20, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Grouping By One Field When Selecting Several

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.

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 Users
GROUP BY email

emailcount email                     
---------- ----------------------- 
2          asmith@devx.com
1          jjones@devx.com
1          mmurrary@microsoft.com
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 Users
GROUP BY email, firstname, lastname

emailcount email                  firstname lastname   
---------- ---------------------- --------- --------
2          asmith@devx.com        Anne      Smith
1          jjones@devx.com        Jack      Jones
1          mmurrary@microsoft.com Mary      Murray
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date