CODEWITHSUNDEEP
sqljoincount
user3258383
user3258383

Reputation: 39

How to use the value in one table to count results in another table?

I want to add a new column on my SQL query that will count the number of times a value on the row appears in a different table.

For example, let's say I have these two separate tables.

Table Name: Table_A

Id   | Coach  | Team_Color  | Team_Number
------------------------------------------
001  | Jane   | Orange      | 121
002  | Frank  | Purple      | 232
003  | Tim    | Red         | 343

Table Name: Table_B

Id   | Team_Number | Player_Name  
----------------------------------
901  | 121         | Jimmy  
902  | 121         | Wesley  
903  | 121         | Samantha
904  | 121         | Wendy
905  | 232         | Tim  
906  | 232         | Sean  
907  | 343         | Andrew
908  | 343         | Erik
909  | 343         | Sarah  
910  | 343         | Allison  
911  | 343         | Desmond
912  | 343         | Kathryn

I want to end up with something like this:

Id   | Coach  | Team_Color  | Team_Number | Player Count
--------------------------------------------------------
001  | Jane   | Orange      | 121         | 4
002  | Frank  | Purple      | 232         | 2
003  | Tim    | Red         | 343         | 6

The new column called "Player Count" is referencing the Team_Number value in Table_A, and counting the number of instances found on Table_B. How would I compose this into one query?

Upvotes: 1

Views: 77

Answers (1)

Nishant Gupta
Nishant Gupta

Reputation: 3656

Solution to your Problem:

SELECT A.Id, A.Coach, A.Team_Color,A.Team_Number,Count(B.Id) AS Player_Count
FROM Table_A AS A
INNER JOIN Table_B B
ON A.Team_Number = B.Team_Number
GROUP BY A.Id, A.Coach, A.Team_Color,A.Team_Number;

OUTPUT:

Id  Coach   Team_Color  Team_Number  Player_Count
1   Jane    Orange       121          4
2   Frank   Purple       232          2
3   Tim     Red          343          6

Follow the link to the demo:

http://sqlfiddle.com/#!9/9c6da4/1

EXPLAINATION:

In your problem, you have to use JOIN to join the two tables on a common Column i.e.Team_Number. Now after Joining you will get a result like this:

Id  Coach  Team_Color  Team_Number  Id   Team_Number    Player_Name
1   Jane    Orange       121        901    121           Jimmy
1   Jane    Orange       121        902    121           Wesley
1   Jane    Orange       121        903    121           Samantha
1   Jane    Orange       121        904    121           Wendy
2   Frank   Purple       232        905    232           Tim
2   Frank   Purple       232        906    232           Sean
3   Tim     Red          343        907    343           Andrew
3   Tim     Red          343        908    343           Erik
3   Tim     Red          343        909    343           Sarah
3   Tim     Red          343        910    343           Allison
3   Tim     Red          343        911    343           Desmond
3   Tim     Red          343        912    343           Kathryn

Now Use aggregate function COUNT on above Result to get the final result.

Upvotes: 3

Related Questions