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

Question

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!

Answer 1

This seems totally sufficient:

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

It does leave ambiguous any subsequent level of ordering beyond color count, but that's easy enough to customize by adding to the Order clause.

Answer 2

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