Sql Distinct Count of Duplicates

Question

I am doing a query on a mysql database. I have a main table where users are stored and another table where friends of that user are stored. For each user I want to see how many friends they have. This is what I'm getting.

ID    FirstName LastName FriendID
1     Andrew    Smith     1
1     Andrew    Smith     5
1     Andrew    Smith     9
2     John      Doe       3
2     John      Doe       5

This is what I want to get.

ID    FirstName LastName Friends
1     Andrew    Smith     3
2     John      Doe       2

If this is not enough detail to go on let me know and I will also show the tables and query I used.

Is it right that John Doe (ID 2) maps to the same FriendID (3) twice and count as 2 friends? — Pero P.
– Pero P., Commented Nov 24, 2010 at 22:25
Oh you're right, updated. I was trying to do the count without using the group by clause — andrew
– andrew, Commented Nov 25, 2010 at 17:43

Larry Lustig · Accepted Answer · 2010-11-24 22:24:21Z

4

 SELECT ID, FirstName, LastName, COUNT(FriendID) AS Friends
    FROM Users GROUP BY ID, FirstName, LastName

One hopes you're not really storing FirstName and LastName in every UserFriendLink record. If you are, it's time to normalize your database with a Users table (with ID, First, and Last) and a UserFriendsLink table (with UserID and FriendID).

answered Nov 24, 2010 at 22:24

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

andrew Over a year ago

In this example we can assume that users have lots of info associated with them. And that friends will never be users. My actual application is a bulletin board where there are posts and post replies. <br />for every post there can be multiple replies, replies have less fields associated with them and a reply will never be a post. but the example I gave above illustrates that relationship

andrew Over a year ago

The table I showed above was the result of my left join query which joined a few normalised tables

andrew Over a year ago

Thanks for the help, it was the group by that I was missing

Collectives™ on Stack Overflow

Sql Distinct Count of Duplicates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related