0

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!

  • Please tag your question with proper RDBMS and Version ! – Kin Shah Sep 15 '15 at 19:12
  • Thanks for the suggestion. I updated the tags to reflect that I'm working with SQLite right now. But isn't a query like this supposed to be portable between DBs that support SQL? – Bobak Hashemi Sep 15 '15 at 19:23
0

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.