By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

Why is it that "Group by" eliminates duplicate records?

P: 1
I'm fairly new to detailed data analysis. A lot of my queries end up using the Group By function, but other than knowing when to use it, I don't understand how it works. All I'm doing is hitting the sum key. All of my fields then show the Group By listed, so I can't figure out why it works the way I need it to. Can someone please explain this function to me? Just in case you need to know, I'm using Access 2000. I realize that knowing that it is working is a good thing, but it sure would be nice to know why. Thanks for some assistance.
Kelly
Nov 12 '07 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
I'm fairly new to detailed data analysis. A lot of my queries end up using the Group By function, but other than knowing when to use it, I don't understand how it works. All I'm doing is hitting the sum key. All of my fields then show the Group By listed, so I can't figure out why it works the way I need it to. Can someone please explain this function to me? Just in case you need to know, I'm using Access 2000. I realize that knowing that it is working is a good thing, but it sure would be nice to know why. Thanks for some assistance.
Kelly
Hi

Using GROUP BY on ALL fields is a waste of time. It does eliminate duplicate records, but this can be done with normal query and the DISTINCT operater (or DISTINCT ROW ?).

You should use GROUP BY when you want to use an aggregate function ie. Sum(), Ave() Count() etc. The fields used to group on will determine which records are aggregated (summed, averaged, counted etc.).

Does that shed any light ?

MTB
Nov 13 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
As Mike says ...

Essentially you only use group by on aggregate functions like sum(), count(), etc.

In the following query ...

Expand|Select|Wrap|Line Numbers
  1. SELECT MemberID, Sum(Fees)
  2. FROM tblMembers
  3. GROUP BY MemberID;
  4.  
You are basically saying that you would like to know the total fees paid by each member. By grouping the MemberID field you are simply saying get the sum (total) of the fees paid by each member.
Nov 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.