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

countjoin;sqlite

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!

Best Answer

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.