How to get a proper count in sql server when retrieving a lot of fields?

Question

Here is my scenario,

I have query that returns a lot of fields. One of the fields is called ID and I want to group by ID and show a count in descending order. However, since I am bringing back more fields, it becomes harder to show a true count because I have to group by those other fields. Here is an example of what I am trying to do. If I just have 2 fields (ID, color) and I group by color, I may end up with something like this:

ID COLOR COUNT

== ===== =====

2    red     10

3    blue     5

4    green   24

Lets say I add another field which is actually the same person, but they have a different spelling of their name which throws the count off, so I might have something like this:

ID COLOR NAME COUNT

== ===== ====== =====

2    Red    Jim      5

2    Red    Jimmy      5

3    Red    Bob      3

3    Red    Robert      2

4    Red    Johnny      12

4    Red    John      12

I want to be able to bring back ID, Color, Name, and Count, but display the counts like in the first table. Is there a way to do this using the ID?

Answer 1

Try doing a sub query to get the count.

-- MarkusQ

Answer 2

If you want a single result set, you would have to omit the name, as in your first post

SELECT Id, Color, COUNT(*)
FROM YourTable
GROUP By Id, Color

Now, you could get your desired functionality with a subquery, although not elegant

SELECT Id, Color Name, (SELECT COUNT(*) 
                        FROM YourTable 
                        Where Id = O.Id 
                            AND Color = O.Color
                       ) AS "Count"
FROM YourTable O
GROUP BY Id, Color, Name

This should work as you desire

Answer 3

Try this:-

 SELECT DISTINCT a.ID, a.Color, a.Name, b.Count
 FROM yourTable
 INNER JOIN (
     SELECT ID, Color, Count(1) [Count] FROM yourTable
     GROUP BY ID, Color
 ) b ON a.ID = b.ID, a.Color = b.Color
 ORDER BY [Count] DESC