Is there a more satisfactory query to get the rows of 'A' ordered by the number of rows of 'B' associated with that row of A in SQL Suppose I have two tables A and B. Table A has a primary key, A.ID for instance, and each row in B contains a reference to some A.ID (there is a foreign key column in B). As a specific example:

A:

ID | Name | Age
1  | Bob  | 25
2  | Dan  | 30
3  | Tina | 20
4  | Val  | 36
5  | Joe  | 51
6  | Mel  | 22


B:

AID  |  Color 
1     |  Blue
1     |  Green
1     |  Black
3     |  Green
4     |  Blue
2     |  Red
5     |  Green
3     |  Red


Now Suppose I want to get the rows of A amended and ordered by the count of references each row has in B. In this example I would want 

ID | Name | Age | Count
1  | Bob  | 25  | 3
3  | Tina | 20  | 2
2  | Dan  | 30  | 1
4  | Val  | 36  | 1
5  | Joe  | 51  | 1
6  | Mel  | 22  | 0


One way I've basically managed to do this is the following query:

Select Count(*), A.ID, A.Name, A.Age, B.Color From 
  B Left Join A
  On B.AID=A.ID
  Group By A.ID
  Order By Count(*) Desc, B.Color Desc


That will return:

Count | A.ID | A.Name | A.Age | B.Color
3     | 1    | Bob    | 25    | Blue(not actually sure what color would go here)
2     | 3    | Tina   | 20    | Green    
1     | 2    | Dan    | 30    | Red
1     | 4    | Val    | 36    | Blue
1     | 5    | Joe    | 51    | Green
1     | 6    | Mel    | 22    | NULL


This works because I can guarantee that the null values will show up after the rows that do have a non-null color due to the order by. However, the counts here are not exactly what I'm looking for, I'd prefer to have Mel's row display a 0 for the count. 

I'm sure there is a better way to perform this type of query, does anyone have any suggestions on how I could make this cleaner?

Thanks!
You can either use the suggestion suggested by @Thomas Cleberg. Count([attribute]) will only count the rows where [attribute] is not null, whereas count(*) will count all rows

Another option is to do the count in a sub select:

select id, name, age, cnt
from (
    select A.ID, 
           A.Name, 
           A.Age, 
           ( select Count(*) from B where B.AID=A.ID ) AS Cnt
    FROM A
) as T
Order By cnt Desc


I've added a nested level for convenience with the order by