Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

Grouping By One Field When Selecting Several

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 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.

 

 

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