Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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